Excel if-then: how the IF function works

IF is one of the most popular features in Excel. Sometimes in a spreadsheet, something should be done if certain conditions are in place. What is known as an “if” statement can be solved with an “if-then” statement in Excel. The principle is the same: if a value is true, then do something, otherwise do something else. Here, we explain how if-then in Excel works and the steps you need to take to execute it.

Excel with Office 365 and 1&1 IONOS!

Use Excel to create spreadsheets and organize your data - included in all Office 365 package!

Office Online
OneDrive with 1TB
24/7 support

What is the if-then statement in Excel?

With the IF formula (to give it its proper name), you can make a logical comparison: Is the requested value identical to what you expect or not? If this query is true, then it triggers an action of your choice. If it’s false, it triggers a different action. Simply put: if A, then B, otherwise C.

This makes if-then one of Excel’s most important tools, which can be used in all sorts of situations. Whether you are working on a simple membership directory, a table, or corporate key figures, the IF formula is highly effective – both alone and in combination with other functions.

If-then in Excel: how does it work?

Like every function and formula in Excel, IF is based on a specific syntax:

                =IF(condition, value_if_true, value_if_false)

As shown above, the function has three parameters, the first two of which are compulsory.

  • Condition: This position must contain a condition – a comparison between two values – where one or both values can be cell references. The possible conditions are:
    • Equal (=)
    • Unequal (<>)
    • Less than (<)
    • Greater than (>)
    • Less than or equal to (<=)
    • Greater than or equal to (>=)
  • Then_value (value_if_true): In this parameter, enter what should happen if the condition is true. Values, character strings, cell references, and other functions can all serve as results – these should be entered in quotation marks.
  • Else_value (value_if_false): This final parameter is optional. If you don’t specify anything here, the function will return FALSE. Otherwise, the else_value behaves in the same way as the then_value.

In practice, an Excel if-then statement can look like this:

                =IF(A1>=100,”target achieved”,”target not achieved”)

Tip

In Excel, functions in the formula bar always start with an equals sign and the parameters are embedded in parentheses. You can also make absolute cell references by using a dollar sign. This means that functions will always refer to the specified cell, even when copied to other cells.

You don’t have to enter the IF function into a cell or the formula bar manually. If you like, you can use the “Insert function” feature, which helps you fill out formulas correctly.

Excel if-then explained with an example

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”)

Note

Since the call reference isn’t definitively set in the function, you can copy the formula into further lines by dragging the fill box. The references are then automatically modified.

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))

Note

The nesting of the IF function if very practical, but it has its limits: Theoretically, Excel if-then functions can be processed 64 times within one form. This isn’t recommended, though. The nesting creates a complexity that makes it difficult to create error-free syntax. For example, you can easily forget one or more parentheses. In such a long form, searching for errors also takes a lot of work. If need be, it also makes sense to work with support columns or other functions like “VLOOKUP”. The IFS functions can also be another solution to the nesting problem.

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”)

IFS: extension of the if-then

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.

Excel with Office 365 and 1&1 IONOS!

Use Excel to create spreadsheets and organize your data - included in all Office 365 package!

Office Online
OneDrive with 1TB
24/7 support
1