The IF function (or "IF statement") in Excel is a simple function that allows you to build logic into your worksheets. The IF function evaluates a logical test and returns one value if the logical test condition is true, and another value if the condition is false. Learning this function will help you begin to write complex logic to power your spreadsheets and models. For example, if a cell in your spreadsheet contains the top speed of a vehicle, you might write an IF statement which returns the text "fast" if the speed is above 30, and returns the text "slow" if the speed if below 30.
The IF function takes three arguments (function inputs inside the parenthesis separated by commas):
=IF(condition, value_if_true, [value_if_false])The value_if_true and value_if_false arguments can be any type of values, including cell references, numbers, text, dates, as well as other functions or formulas.
You may be wondering how to write a logical comparison for the first part of your IF statement. To compare two values, use one of the six logical comparison operators from the table below.
You can use these comparison operators to compare text values, dates, numbers, and more.
| OPERATOR | MEANING |
| = | Equal to |
| <> | Not equal to |
| < | Less than |
| > | Greater than |
| <= | Less than or equal to |
| >= | Greater than or equal to |
See the table below for some examples of logical comparisons in Excel.
| LOGICAL STATEMENT | RESULT |
| 1=1 | True |
| 1=2 | False |
| "Hello" <> "Goodbye" | True |
| 5<>5 | False |
| 1/1/2021 > 1/1/2020 | True |
| 5>6 | False |
| 10<20 | True |
| 5/1/2020 < 4/1/2020 | False |
| 10<=20 | True |
| 10<=10 | True |
| 5>=1 | True |
| 5>=6 | False |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Speed: | 35 | |||
| 3 | |||||
| 4 |
Recall the syntax of an IF statement:
=IF(condition, value_if_true, value_if_false)The first argument is the condition, or the logical test that compares two different values. If the condition is true, Excel returns the second argument (shown above as value_if_true). If the condition is false, Excel returns the third argument (shown above as value_if_false). See the example below:
=IF(C2>30, "Fast", "Slow")The IF statement above compares the value in cell C2 to 30. Excel returns the text "Fast" if speed is greater than 30 (because the condition is true), and returns the text "Slow" if speed is NOT greater than 30 (because the condition is false).
You could also omit the third argument:
=IF(C2>30, "Fast")This IF statement would display the word "Fast" if speed was greater than 30, and would otherwise simply be blank. This is because the IF statement does not have the value_if_false argument.
You saw how an IF statement can return text values, but we can also combine any other function in Excel with any of the arguments of an IF statement. 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 |
=IF(SUM(C2, C3)>=500, SUM(C2, C3)*0.1, 0)The formula above says If Sales for TX + CA is greater than or equal to 500, then return 10% of the total. Otherwise, return 0.
Because the SUM function in the condition would return 865, which is greater than 500, our IF statement would return the value_if_true argument. The value_if_true argument returns the sum multiplied by 0.1, so this IF statement would return 86.5.
You can combine any number of IF Statements in Excel by "nesting" them inside the second or third arguments of another IF statement. For example, say you are shopping for a new car. You want a newer car, but you also like older Cadillacs. You decide to look for a car that meets the following conditions:
Below we see different car options in a table. We can write a nested IF statement, starting with row 2, to help you determine whether to buy the car or not.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | COLOR | YEAR | MAKE | ||
| 2 | Green | 1999 | Honda | ||
| 3 | Blue | 2009 | Caddilac | ||
| 4 | Red | 2015 | Chevy |
=IF(C2="Cadillac", "Buy", IF(B2>2010, "Buy", "Do not buy"))The IF statement above says If make is equal to "Cadillac", then buy it. Otherwise, if the year is greater than 2010, then buy it. Otherwise do not buy it.
Starting with row 2, the make is not Cadillac, so we then go to the value_if_false clause. The year is not greater than 2010, so we would not buy the car.
For row 3, the make is Cadillac, so we would buy the car.
In row 4, the make is not Cadillac so we go to the value_if_false clause again. This time the year is greater than 2010, so we would buy this car.
Excel also lets you use the AND function and OR function as conditions of the IF function as a way to expedite the process of writing multiple conditions.
Say you want your IF statement to trigger the value_if_true_ argument only if several different conditions are all true. The AND function takes comparisons as its arguments and returns TRUE if ALL of the arguments are true. For example, the function =AND(A1>B1, A2=B2, A3<=B3) will return TRUE only if ALL three arguments are true. It will return FALSE if any of its arguments is false.
Now imagine you want your IF statement to trigger the value_if_true_ argument if at least one of several different conditions is true. The OR function also takes comparisons as its arguments and returns TRUE if AT LEAST ONE of the arguments is true. For example, the function =OR(A1>B1, A2=B2, A3<=B3) will return TRUE if AT LEAST ONE of the three arguments is true. It will return FALSE only if ALL of the arguments are false.
To use AND or OR with an IF function, you simply replace the condition argument with the AND or OR function:
=IF(AND(condition_1, [condition_2]...), value_if_true, value_if_false)=IF(OR(condition_1, [condition_2]...), value_if_true, value_if_false)