The syntax of the IF() function is the following:
= IF ( logical_test , [value_if_TRUE] , [value_if_FALSE] )
The first two attributes are obligatory, i.e. without them, the formula doesn’t work, and produces an error message.
- logical _test – a placeholder for the condition we want to test
- value_if_TRUE – an attribute where we need to specify a value (text, or another function) that the IF formula returns when the result of the logical_test is TRUE
The third parameter is Optional, i.e. we can skip it and in such a case, the IF() function returns the default value FALSE.
- value_if_FALSE – an attribute for a value, text or another formula that the IF() function must return when the result of the logical test is FALSE.
In other words, IF() function is used to test the specific conditions and can be described as the statement:
IF something is true, THEN return the first value, ELSE – return then second value.
The simplest IF() function may be written with the first two parameters only.
For example, this formula is absolutely correct:
This formula tests the condition we set (C2=1) and if it is true, the formula returns the text message (“Hello!”).
Otherwise, it returns the default value FALSE.
Interesting Fact!!! You can use another simpler test in Excel for a certain condition. Just place a formula like “=C2>C3” in a cell and press ENTER. If this statement is true, the formula returns the default value – TRUE. Else (i.e. when a value in C2 is NOT larger than a value in C3 cell) the formula will return another default value – FALSE. We use the IF() function in these cases; when we want to trigger some further actions or calculations depending on the results of the logical test.
Typically, the IF() function is used in situations when we need to calculate or flag the results based on certain conditions (for example, smaller than, equal to, greater than and others).
Despite the fact that Excel usually provides multiple choices on how to solve every specific task, the IF() function can play a good role in such cases.
Another simple example of the IF() function:
If a value of the C3 cell is the text “Hello!” then this formula returns the one (1) value.
Else (i.e. if this cell contains any other text, number or blank value), the formula returns zero.
The big advantage of the IF() function (like many other Excel functions) is how we can replace all three arguments in this example with whatever we want/need and it still works properly (if we follow the syntax, of course).
For example, let’s replace the one (as a value_if_true) to another formula “C3+C4” and replace zero value (0) to TODAY() function.
Our initial formula becomes:
That means – IF a value in C2 cell equals one, THEN do the addition C2+C3.
ELSE (i.e. when C2 doesn’t equal “Hello!”), THEN execute the TODAY() function and return its result.
IF() function is a universal function, i.e. it accepts as an attribute different types of a value, such as numeric value, text, date/time, other functions, named ranges and others).
The IF function can be easily used as a part of other more complex formulas.
Moreover, you can use the so-called nested IF() function and replace the [value_if_false] with another IF() function.
The most common reason to do that is when you need to create a multi-choice comparison like a waterfall.
The last value_if_FALSE attribute is a value that we want the formula to return if the result of ALL logical tests in the formula is FALSE.
We know that we need to use a nested IF() (i.e. one IF() inside another IF() statement) when we have a “but” keyword inside the sentence with a logical test.
Take a look at the screenshot below.
In this example, we need to add the text “flag” if a value is greater than 200, BUT if this value is less than 50 we need to return “follow up” text.
We know that we need to use a nested IF() when there is the “but” keyword in the logical test.
Pro Tip: Be cautious. The more IF iterations we use, the more complex the formula becomes and the more difficult it is to understand. In addition, the complex formulas that contain multiple IF functions along with other Excel functions may slow down the calculation speed, especially if they use large ranges of cells. Also, it’s very easy to make an accidental mistake or typo inside the long and complex formulas, and such errors are difficult to trace properly.
The IF() function is often used with other logical functions such as AND() or OR() functions that we will discuss further.
Let’s look at some practical examples…