The IF function, or IF statement, in Excel returns one value if a condition is true, and another value if the condition is false. Learning this function will help you begin to write complex logic to power spreadsheets and models.
A | B | C | D | E | |
---|---|---|---|---|---|
1 | |||||
2 | Speed: | 35 | |||
3 | |||||
4 |
The IF function takes three arguments:
=IF(condition, value_if_true, value_if_false).
The first argument is the condition. If the condition is true, Excel returns the value_if_true. If the condition is false, it returns the value_if_false.
The formula above returns "Fast" if speed is above 30 (because the condition is true), and otherwise returns "Slow" (because the condition is false).
When writing the condition for your IF statement you can use a number of different comparison operators:
Operator | Description | |
---|---|---|
1 | > | Greater than |
2 | < | Less than |
3 | >= | Greater than or equal to |
4 | <= | Less than or equal to |
5 | = | Equal to |
6 | <> | Not equal to |
You can combine any other function in Excel with any of the arguments of IF. For example, take this exercise using the SUM function: IF Texas and California sales combined are at least $500, commission is 10% on the total. Otherwise Commission is 0.
A | B | C | D | E | |
---|---|---|---|---|---|
1 | |||||
2 | TX Sales: | $320 | |||
3 | CA Sales: | $545 | |||
4 |
The formula above says If Sales for TX + CA is greater than or equal to 500, then return 10%. Otherwise, return 0.
You can combine any number of IF functions in Excel by "nesting" them inside the result arguments of another IF function. Take the example below: If the year is greater than 2000, return 1. Otherwise, if color is pink return 2. Otherwise (if year is not greater than 2000 and color is not pink), return 0.
A | B | C | D | E | |
---|---|---|---|---|---|
1 | |||||
2 | Year: | 1996 | |||
3 | Color: | Green | |||
4 |
The formula above says If year is greater than 2000, then return 1. Otherwise, if color is pink return 2 and otherwise return 0.
As with all shortcuts and formulas, the best way to improve is to try as many hands-on practice exercises as possible. The more repetitions you get, the more quickly and naturally you'll be able to use them in your everyday work.
Try some Excel Exercises with the IF function now!