IF
Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
Use IF to conduct conditional tests on values and formulas.
Syntax 1
IF(logical_test,value_if_true,value_if_false)
Logical_test is any value or expression that can be evaluated to TRUE or FALSE.
Value_if_true is the value that is returned if logical_test is TRUE. If logical_test is TRUE and value_if_true is omitted, TRUE is returned. Value_if_true can be another formula.
Value_if_false is the value that is returned if logical_test is FALSE. If logical_test is FALSE and value_if_false is omitted, FALSE is returned. Value_if_false can be another formula.
Remarks
- Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the following last example.
- When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.
- If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out. If some of the value_if_true and value_if_false arguments are action-taking functions, all of the actions are taken.
Examples
In the following example, if the value in cell A10 is 100, then logical_test is TRUE, and the total value for the range B5:B15 is calculated. Otherwise, logical_test is FALSE, and empty text ("") is returned that blanks the cell that contains the IF function.
IF(A10=100,SUM(B5:B15),"")
Suppose an expense worksheet contains in B2:B4 the following data for "Actual Expenses" for January, February, and March: 1500, 500, 500. C2:C4 contains the following data for "Predicted Expenses" for the same periods: 900, 900, 925.
You can write a formula to check whether you are over budget for a particular month, generating text for a message with the following formulas:
IF(B2>C2,"Over Budget","OK")
equals "Over Budget"
IF(B3>C3,"Over Budget","OK")
equals "OK"
Suppose you want to assign letter grades to numbers referenced by the name AverageScore. See the following table.
If AverageScore is |
Then return | |
Greater than 89 |
A | |
From 80 to 89 |
B | |
From 70 to 79 |
C | |
From 60 to 69 |
D | |
Less than 60 |
F |
You can use the following nested IF function:
IF(AverageScore>89,"A",IF(AverageScore>79,"B",
IF(AverageScore>69,"C",IF(AverageScore>59,"D","F"))))
In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.