Overview
The Excel IF function tests a given condition and returns one value for a TRUE result and another value for a FALSE result. For example, we could determine if someone passed or failed a test where the passing grade is 50: =IF(A1<50, “Fail”, “Pass”). The IF function can be used to evaluate a single condition or we can use multiple IF functions in the same formula, to test for multiple conditions. Including multiple IF functions in a single formula is known as nesting IF functions.
Syntax
=IF(logical_test, [value_if_true], [value_if_false])
Arguments
logical_test – a logical expression that can be evaluated as TRUE or FALSE
value_if_true – [optional] the value if the logical test returns TRUE
value_if_false – [optional] the value if the logical test returns FALSE
Version
Excel 2003 and onward
Purpose
The Excel IF functions runs a logical test on a given input and returns one value for a TRUE result and another value for a FALSE result. The IF function is one of the most widely used functions in Excel, mainly due to it’s simplicity and versatility. The first argument, the logical_test, is an expression that should be evaluated as being either TRUE or FALSE. The second argument, the value_if_true, is what Excel will return if the logical test is evaluated to be TRUE. The third argument, the value_if_false, is what Excel will return if the logical test is evaluated to be FALSE.
For example, assuming the value in cell A1 is 40, we could use the IF function accordingly:
=IF(A1 < 50, “Fail”, “Pass”) // returns Fail
In the example above, the IF statement essentially says: if the value in cell A1 is less than 50, return “Fail”, otherwise, return “Pass”. Since we know the value in cell A1 is 40, the formula returns “Fail”.
Conversely, if the value in cell A1 is 70, the result would be as follows:
=IF(A1 < 50, “Fail”, “Pass”) // returns Pass
Now we see the same formula return “Pass”, because the value in cell A1 is not less than 50.
If the value_if_true and value_if_false arguments are left blank, Excel will default the result to be either TRUE or FALSE. Again, assuming the value in cell A1 is 40, we could use the IF function accordingly:
=IF(A1 < 50) // returns TRUE
Conversely, if the value in cell A1 is 70, the result would be as follows:
=IF(A1 < 50) // returns FALSE
Before we get into some practical examples of the IF function, let’s first take a look at all of the different comparison logical operators that can be used in the logical_test.
| Logical Operator | Meaning | Example |
|---|---|---|
| = | equal to | A1 = B1 |
| <> | not equal to | A1 <> B1 |
| < | less than | A1 < B1 |
| <= | less than or equal to | A1 <= B1 |
| > | greater than | A1 > B1 |
| >= | greater than or equal to | A1 >= B1 |
For all of the operators above, the examples read from left-to-right. For example, the equal sign means that the value in cell A1 is equal to the value in cell B1. Similarly, for the greater than sign, the value in cell A1 is greater than the value in cell B1.
The IF function does not support wildcards.
Examples
Let’s look at a few examples of how the IF function works.
First, let’s explore an example where we are trying to determine if an individual qualifies for a bonus based on their sales for the month. Assuming the individual needs to sell over $30,000 worth of product in a month to qualify for a $5,000 sales bonus, we can use the following IF statement:
=IF(B5 > 30000, 5000, 0)
Since the value in cell B5 is less than $30,000, the formula evaluates to FALSE, or $0 in this case. If the individual were to sell over $30,000, the result would look as follows:
Note that when using the IF function, the value_if_true and value_if_false arguments only need to be bounded by quotation marks if the results are string values. If they are numerical values, they do not need to be bounded by quotation marks. See below:
Now let’s say we want to take the first example a step further, and tier the bonus amount based on how much the individual sells within a month. This can be done through nested IF statements. A nested IF statement is essentially just an IF function within an IF function. The syntax would look like:
Here we see that the value_if_false in the first IF function has been replaced by another IF function. This is what nesting IF functions looks like. Depending on how many conditions you would like to evaluate for in your formula, you could continue to nest IF functions, as required.
Let’s now assume there are two tiers for attaining a sales bonus. If the individual sells more than $20,000 in a month, they will earn a $2,000 bonus. If the individual sells more than $30,000 in a month, they will earn a $5,000 bonus. The nested IF formula would look like:
Since the individual did not sell greater than $30,000, they did not meet the first IF function criteria and it evaluated as FALSE. The FALSE condition was another IF function, which checked to see if the individual sold more than $20,000. Since the individual sold $25,000, this second IF function evaluated as TRUE, which resulted in the $2,000 bonus.
Notes
- The IF function is not case sensitive
- The IF function will be default evaluate to either TRUE or FALSE if the optional arguments are not entered
- The IF function does not support wildcards
Author
Kyle Stott
Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for almost a decade and has been consulting on and teaching best practices in Microsoft Excel for several years.
Want to learn more?
Join My Excel Academy Today!
This All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
30-Day Money-Back Guarantee
This all-access pass includes:
- Access to all My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
Don’t hesitate – join now!
