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
IF(logical_test,value_if_true,value_if_false)
Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, [Quarter1]=100 is a logical expression; if the value in one row of the column, [Quarter1], is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.
Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.
Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) 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 last of the following examples.
-
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.
Example set 1
|
C ol1 |
Col2 |
Col3 |
Expense |
Formula |
Description (Result) |
|---|---|---|---|---|---|
|
50 |
=IF([Expense]<=100,"Within budget","Over budget") |
If the number is less than or equal to 100, then the formula displays "Within budget". Otherwise, the function displays "Over budget". (Within budget) |
|||
|
23 |
45 |
89 |
50 |
=IF([Expense]=100,SUM([Col1],[Col2],[Col3]),"") |
If the number is 100, then the three values are added. Otherwise, empty text ("") is returned. () |
Example set 2
|
ActualExpenses |
PredictedExpenses |
Formula |
Description (Result) |
|---|---|---|---|
|
1500 |
900 |
=IF([ActualExpenses]>[PredictedExpenses],"Over Budget","OK") |
Checks whether the first row is over budget (Over Budget) |
|
500 |
900 |
=IF([ActualExpenses]>[PredictedExpenses],"Over Budget","OK") |
Checks whether the second row is over budget (OK) |
Example set 3
|
Score |
Formula |
Description (Result) |
|---|---|---|
|
45 |
=IF([Score]>89,"A",IF([Score]>79,"B", IF([Score]>69,"C",IF([Score]>59,"D","F")))) |
Assigns a letter grade to the first score (F) |
|
90 |
=IF([Score]>89,"A",IF([Score]>79,"B", IF([Score]>69,"C",IF([Score]>59,"D","F")))) |
Assigns a letter grade to the second score (A) |
|
78 |
=IF([Score]>89,"A",IF([Score]>79,"B", IF([Score]>69,"C",IF([Score]>59,"D","F")))) |
Assigns a letter grade to the third score (C) |
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 ([Score]>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.
The letter grades are assigned to numbers using the following key (assuming use of integers only).
|
If Score 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 |
