Using the IF Function
IF Function Syntax
The syntax of the IF function is =IF(logical test, value if true, value if false). The logical test is any value or expression that Excel evaluates as true or false. Some examples of expressions are B4>C4, B7<100, and B8<=999.
Suppose you want to display a warning message if the nutrient content of Vitamin A in cell B4 is less than 50% and display the message “Value is at least 50%” if not low. You could use the formula: =IF(B4<50%, “Value is less than 50%!”,”Value is at least 50%”).
Practice: Using the IF Function to Evaluate a Product
Open the file Slim.xls provided. This worksheet lists the percentage of the U.S. daily values (USDV) of sixteen essential vitamins and minerals, electrolytes, and trace elements in the two body-building supplements. An IF function could automatically compare the USDV’s for each nutrient and calculate which supplement has more.
Use the IF function to calculate whether the Slim Slurp product or the PowerPunch product contains the higher potency of each nutrient.
- In cell D4, enter the IF function that indicates which product has the higher potency of Vitamin A: =IF(B4>C4, “Slim Slurp”, “PowerPunch”)
- Make sure the text Slim Slurp appears in cell D4.
- If the text in cell D4 is correct, replicate this formula from cell D4 through cell D20.
- Next, enter a formula using the IF function that prints “Low” if any PowerPunch nutrient falls below 75%, but if the value is at least 75%, it prints a blank. To print a blank, just put a space in between the quotation marks.
- Save your work.
Nested IF Statements
Remember that the IF function returns one value if a condition is TRUE and another value if it is FALSE. From the previous exercise, you entered an IF function using the syntax =IF(logical_test,value_if_true,value_if_false). A logical test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates as TRUE. Otherwise, the expression evaluates as FALSE. This argument can use any comparison calculation operator:
| Comparison operator | Meaning (Example) |
= (equal sign) |
Equal to (A1=B1) |
> (greater than sign) |
Greater than (A1>B1) |
< (less than sign) |
Less than (A1<B1) |
>= (greater than or equal to sign) |
Greater than or equal to (A1>=B1) |
<= (less than or equal to sign) |
Less than or equal to (A1<=B1) |
<> (not equal to sign) |
Not equal to (A1<>B1) |
Value_if_true is the value that is returned if the 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 the logical test is TRUE and the value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Also, the value_if_true can be another formula.
Value_if_false is the value that is returned if the 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 the 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 the 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.
- 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 (an ordered arrangement of data elements), every element of the array is evaluated when the IF statement is carried out.
- Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF worksheet function. To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function.
Practice: Entering Nested IF Statements
- Enter five test scores in column A as shown below:
- In cell B2, enter the following formula using nested IF statements to assign a letter grade to the first score.
=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F"))))
- Autofill the formulas by dragging down from the right bottom corner of the cell when the cursor changes to a black cross to complete the column as shown and save your work.
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.
The letter grades are assigned to numbers using the following key.
| 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 |
More about Nesting Functions within Functions
In certain cases, you may need to use a function as one of the arguments of another function. For example, the following formula uses a nested AVERAGE function and compares the result with the value 50.
Valid returns When a nested function is used as an argument, it must return the same type of value that the argument uses. For example, if the argument returns a TRUE or FALSE value, then the nested function must return a TRUE or FALSE. If it doesn't, Microsoft Excel displays a #VALUE! error value.
Nesting level limits A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For instance, the AVERAGE function and the SUM function are both second-level functions because they are arguments of the IF function. A function nested within the AVERAGE function would be a third-level function, and so on.
Click here to return to Course Topics.