Excel if-then: how the IF function works

The IF function can be used in various situations, for example, when sorting data points. The following example is an inventory. With IF, you can create a market to indicate when a certain minimum stock level is reached.

                =IF(D2<10,”Yes,”No”)

In addition to a call reference, it’s also possible to add more functions in the IF. You can add a discount to the original price in case the last sale was too long ago.

                =IF((DAYS(TODAY();F2))>30;C2*0,75;C2)

In this case, the DAYS function is part of the test: The current data is compared with the specified date (in F2) and the difference in the number of days is displayed. IF checks whether the count is higher than 30. If this is the case, the “then_value” where you’ve placed a mathematical calculation occurs: a discount is given to the original value (in cell F2). Otherwise the original value is retained.

Of course, you can also include parameters for then and else. The one drawback of the IF function is that the test only knows two results: TRUE or FALSE. If you want to differentiate between multiple conditions, though, a new IF function can occur instead of the “else_value,” which then performs another check.

In our example, it would be conceivable to give an even higher discount to goods that already have a reduced price if there are only a few of them left. For this, we have two conditions: The last sale took place more than 30 days ago, and there are no more than 10 items in stock. For this reason, we need to place an “AND” link in the test parameter as well as another if-then request in the else parameter, because we want to make three results possible: 50% discount, 25% discount, and no discount.

                =IF(AND(DAYS(TODAY();F2>30;E2<10);C2*0,5;IF(DAYS(TODAY();F2;C2*0,75;C2))

It’s also possible, of course, to incorporate if-then into other Excel functions. This makes sense, for example, in a simple “SUM” function. Imagine the example company wants to find out how many items are currently discounted. In such a situation, you can use this formula:

                =SUM(IF(D2=C2;0;1);IF(D3=C3;0;1);…)

In practice, though, this formula becomes very long. Instead, it’s better to work with a support column in which you again use the simple if-then function, and then set up the “COUNT IF”function. With this, you can only count the cells that correspond to a particular criteria, so for example those that contain the word “Yes”:

                =COUNTIF(E2:E11;“Yes”)

Since Office 2016, Excel has offered yet another if-then function. The IFS function works similarly to IF: A “then_value” is assigned to a logical test – but up to127 times. The syntax looks as follows:

=IFS(Something is True1; Value if True1; [Something is True2; Value if True2];…[Something is True127; Value if True127])

What stands out here: There’s no longer an “else_value”. But this also means that you have to define the reverse yourself – in our example, in the event that no discounts are used. Therefore:

=IFS(AND(DAYS(TODAY();F2)>30;E2<10);C2*0,5;DAYS(TODAY();F2)>30;C2*0,75;DAYS(TODAY();F2)<=30;C2)

The two checks for the discounts happen one after another. This is followed by the else case: All products that were sold less than or equal to 30 days ago.