Quick Reference Card
We all deal with ifs in our daily life. For example, if it is cold you might put on a sweater; if it is hot, you’ll probably take the sweater off. In Excel you use the IF function to deal with situations where you want to see a result based on whether a condition you specify is True, or False. Essentially, after the condition is determined to be true or false, there’s a fork in the road: Excel will take one path if the condition True, or a different path if the condition is False.
- Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example: A2<=100, or A2>B2.
- Value_if_true is the value that is returned if the logical_test is TRUE. If omitted, TRUE is returned.
- Value_if_false is the value that is returned if the logical_test is FALSE. If omitted, FALSE is returned.
Tips If you don’t recall the arguments, Excel can help you out.
- Click the Formulas tab on the ribbon, and then click Insert Function. Type IF in the Search for a function box, click IF in the Select a function list, and then click OK. The Function Arguments dialog box opens, with an explanation for each argument.
- Or you can use Formula AutoComplete to get help. Just type the equal sign (=) and IF(. A screentip appears, with the name of each argument in its proper order. If you’re not sure about an argument, click the name of the function in the screentip to get a Help topic. If Formula AutoComplete does not appear when you type a formula, the feature may have been turned off. To turn it on, click the File tab in Excel, click Options, and then click the Formulas category. Under Working with formulas, select Formula AutoComplete.
For the IF function to work you must use a comparison operator in the Logical_test.
- < less than
- > greater than
- = equals
- <= less than or equal to
- >= greater than or equal to
- <> not equal to
Suppose you are tracking expenses and want to see if figures are within budget, or over budget. In this example, anything less than or equal to $100 is Within budget. Anything over $100 is over budget.
Here’s the formula: =IF(A2<=100,”Within budget”,”Over budget”)
Nested IF example
The previous example has two possible outcomes: Within budget, or over budget. To get additional possible outcomes, you can use more than one IF function in a formula, which is called nesting.
In this example, suppose you need to figure out salary deductions. There are three possible outcomes: that a salary deduction is 6% for salaries less than $25,000, 8%for salaries from $25,000 to $39,999, or 10% for salaries greater than or equal to $40,000.
Here’s the formula: =B7*IF(B7<25000,$B$2,IF(B7<40000,$B$3,$B$4)) Notice that the formula begins with multiplication (B7 times the result of the IF function). Also, the formula contains absolute references to cells B2, B3, and B4.
While you can nest a great number of functions within functions, don’t let your formulas get too complicated. As an alternative to long IF nested formulas, consider using the VLOOKUP function. There’s information about it at the top of the page.