Working with IF Functions in Excel 2016
Excel’s IF function intimidates a lot of people, but it’s really not that conceptually difficult. The IF function determines whether a statement is true or false, and then takes one of two actions depending on the answer.
Let’s look at a plain-English example. Suppose that when the sum of C2 and D2 is greater than 100, you want to display in E2 the result of multiplying the sum of C2 and D2 by 0.05. On the other hand, when C2+D2 is not greater than 100, E2 should show 0.
The syntax for the IF function is:
So first let’s write the condition:
If the condition is true we want to do this math for the value_if_true :
Notice that there are parentheses around the addition operation. Without these parentheses, it wouldn’t be done first; in the order of operations, multiplication comes before addition.
The third part of the function, the value_if_false, is 0.
Filling these values into the function, it looks like this:
If you have more than two possible conditions, and you want something different for each condition, you can nest one IF function inside another. You can handle two of the conditions in the inner IF, and then apply the third condition in the outer IF.
Here’s an example to work through. Suppose that we want to display some text according to the value of C2+D2. When C2+D2 is greater than 100, we want to show the text “Good” and when C2+D2 is greater than 50 but less than 100, we want to show the text “Fair”. When neither of those conditions is true, we want to show the text “Poor”.
We start by evaluating the first condition as the value_if_true:
The value_if_false will be another IF statement:
Putting them together — you omit the = sign for the nested function — looks like this:
Notice the three closing parentheses at the end, one for each IF.
Now that you know how IF functions work, you can string together as many levels of nesting as you need to get the job done.