Welcome to the COMPLETE guide on the Excel If function. We’ll go into formulas containing If, nested If statements, AND and OR. The different conditional operators that can make up the conditions of the If function. If you’re looking for some of the more advanced If functionality, you’re in the right place. So strap in and let’s get started.
The Excel IF Function
Syntax and explanation
The if function looks like this:
=IF(Condition, [Value if true],[Value if false])
Note: the square brackets [ and ] mean that the argument is optional.
The important argument here is the first one, the Condition. A condition is a value that can be evaluated to be either TRUE or FALSE.
For example, 5 = 4 is a condition. Excel will look at 5 = 4 and evaluate it. Meaning it will check if it is true or false. In this case, it will be evaluated to be FALSE. Why? Because 5 is not equal to 4. As you can imagine, the condition 5 = 5 would be evaluated to TRUE. There are no other options for conditions, only TRUE or FALSE.
The second and third arguments of the if function (Value if true and Value if false) are just to show a result based on the evaluated condition.
Different types of conditions
Comparison operators
So we can test equality using the equals sign =. But we can test more:
Inequality using <>, greater than using >, less than using <, greater than or equal to using >= and finally, less than or equal to using <=.
Example
Take a second to look at the following formula:
=IF(5>4,”5 is greater than 4″,”5 is not greater than 4″)
What do you think this will result in?
Click here for the answer
It will result in 5 is greater than 4. That’s because the condition 5 > 4 will evaluate to TRUE. And if the condition is TRUE, then the Value if true argument is the result of the function.
You can try it out for yourself in the interactive spreadsheet editor below.
Another type of condition: functions that evaluate to TRUE or FALSE
You can also use other functions as conditions if they evaluate to TRUE or FALSE. For example, the ISBLANK function.
ISBLANK(Value) takes a value as argument and returns TRUE if the value is blank and FALSE otherwise. You would typically use it to check if a cell is empty. Like so: ISBLANK(A1). If you fill this in in Excel, the result will be TRUE if cell A1 is empty and FALSE otherwise.
You could combine the Isblank function with the If function into something like this:
=IF(ISBLANK(A1), ”Cell A1 is empty”, ”Cell A1 is not empty”)
Cool huh? And you can replace ISBLANK for any function that evaluates to TRUE or FALSE. Like ISERROR, ISTEXT, ISODD and even ISEVEN.
And, Not, Or and Xor
To create more complex conditions, there are a few helper functions. They are AND, OR and XOR. These are functions that take 2 conditions as input and return TRUE or FALSE based on the conditions.
Or
Let’s look at an example:
=OR(5=5,5=4)
So this is the Or function, with 2 conditions given as arguments, 5=5 and 5=4.
If you plug this function into Excel, it will show TRUE. To see why, we have to look at the arguments.
One of these arguments evaluates to TRUE (5=5) and the other to FALSE (5=4). The Or function looks at both arguments and checks if argument 1 is TRUE or if argument 2 is TRUE (or both). If that is the case, Or will return TRUE. Otherwise, it will return FALSE.
Okay, one-sentence recap: OR(a, b) checks if aor bor both are true and then returns TRUE. Otherwise, it returns FALSE.
The And and the Xor functions are very similar. The only difference is for which specific values of a and b they return TRUE.
Xor
XOR is almost the same as OR. XOR(a, b) checks if aor bbut not both are true and then returns TRUE. Otherwise, it returns FALSE.
And
AND(a,b) checks if both aand b are true and then returns TRUE. Otherwise, it returns FALSE.
Not
Not is the exception to the functions we have seen. It is also the simplest. The Not function requires 1 condition as argument and returns the opposite. So NOT(TRUE) will result in FALSE and NOT(FALSE) will result in TRUE.
Nesting IFs
What is nesting?
Nesting basically means putting one inside of the other. It’s kind of like those Russian Matryoshka dolls. You know the dolls that open up and have smaller dolls inside? And those dolls have even smaller dolls inside. It’s like that, but with functions.
We’ve done some nesting already a couple of sections back with the Isblank function:
=IF(ISBLANK(A1), ”Cell A1 is empty”, ”Cell A1 is not empty”)
The Isblank function is nested inside of the If function. That’s all. If you understand what’s going on above, you understand nesting.
Click here if you don’t understand what’s going on
If you don’t understand what’s going on, basically the ISBLANK(A1) argument is a function used as an argument of the IF function. What will happen is that ISBLANK(A1) will be evaluated by Excel. Then, it will be replaced internally by its result. SO either TRUE or FALSE, depending on if the cell A1 is empty. The result is plugged into the If function as the first argument.
So these steps will happen:
1. =IF(ISBLANK(A1), ”Cell A1 is empty”, ”Cell A1 is not empty”)
Suppose cell A1 is empty. That means ISBLANK(A1) will evaluate to TRUE.
2. =IF(TRUE, ”Cell A1 is empty”, ”Cell A1 is not empty”)
The Value if true argument is returned because the value is TRUE.
3. “Cell A1 is empty”
Nesting Ifs inside of Ifs
Nesting If functions inside of If functions can be useful if you want to check for more than 2 things at once.
Suppose you own a shop and want to give a discount based on the number of items someone buys. If cell A2 contains the number of items bought, you could do something like:
=IF(A2 > 10,”5% discount”, ”No discount”)
So a person gets a discount of 5 percent if they purchase more than 10 items.
Nested ifs example
But what happens if you want to add another discount if they buy even more? That’s when you would use nested Ifs:
=IF(A2 > 10, IF(A2 > 20,”10% discount”, ”5% discount”), ”No discount”)
Here it is in the editor:
Take a second to try and understand the formula above. Let’s go through it together.
The If function returns either the second or third argument based on what the first argument is. So we look at the first argument first: A2 > 10. If A2 is not greater than 10, we return the third argument: No discount. If it is greater than 10, we return the second argument: another If function.
When we get to the second If, we know that A2 is greater than 10. Otherwise, we would not get to that function. So we can just check if A2 is bigger than 20. If this is not the case, A2 is between 10 and 20 and we return the small discount (5%). If it is bigger than 20, we return a larger discount (10%).
See how we’ve had to jump between arguments to wrap our heads around this? That’s why nested Ifs can be a pain to understand. Don’t worry though, you will get better at this with practice.
What do you think of the Excel IF function?
Do you have any more If tips? Do you like nested ifs, or hate them? Let us know in the comments.