Excel IF function: YOUR OBJECTIVE
If any of your salespersons sell more of your company’s products and services than the average of total sales made by all salespersons, your company will award them a bonus of 2% of their total sales. Since the value of orders can vary, exactly who receives such a bonus and the amount of the bonus can also vary. You want Excel to calculate which salespersons should receive a bonus and how much it should be. Excel’s IF function can help you make the required calculations because it allows you to specify that a calculation should only be carried out if a certain condition is met.
This learning module “The excel IF function” will show you how to use the excel IF function.
HOW DOES THE EXCEL IF FUNCTION WORK?
The sales information for your company’s salespersons was recorded in the table pictured below. Your intention is to automatically insert a “Yes” or a “No” in cells F4:F9 to indicate whether the particular salesperson is to receive a bonus or not. Because Excel’s IF function calculates the value of a cell depending on whether or not a user-specified condition is met, it is suitable for carrying out this task.
A few words about how the excel IF function works:
- An excel IF function makes the content of the cell in which it has been entered dependent on whether a condition, specified in the excel IF function, has been met.
- Before deciding on the content of the cell, the function first tests the given condition. In our current example, you would want to test “are the sales made by the current salesperson greater than the salespersons’ average sales?”
- The excel IF function develops a value to be displayed in the cell in which it was entered. The displayed value depends on how the given condition evaluates. If the evaluated condition is met, the condition is held to be TRUE; if not met, the condition is held to be FALSE. For the first salesperson in our table, if the value in cell E4 is greater than the value in cell E17, which contains the average value of all sales, then cell F4 is to display “Yes”, otherwise it should display “No”.
The excel IF function takes three arguments, only one of which is formally required. Nonetheless, to avoid unexpected results, it is recommended that you make it a habit to fill in all three arguments, even if a value of “TRUE” or “FALSE” might at first seem useful. In the following illustration, you see what happens in our example if only the Value_if_true argument is filled in. The result looks a bit odd:
In the current example, to determine which salesperson should receive a bonus, begin by clicking in cell F4. Then start the Function Wizard by clicking the button just to the left of the formula bar. In the Insert Function dialog box that opens, you will find the excel IF function in the Logical category. After selecting the excel IF function and clicking OK, the Function Arguments dialog box will appear (shown below). For the Logical_test argument, you can enter the condition: E4>E17. For Value_if_true, enter Yes. For Value_if_false, enter No.
As you can see in the illustration, the Function Wizard automatically adds the required quotation marks around your plain text entries (“Yes” or “No”). If later you manually edit the function in the formula bar or want to enter your text arguments explicitly, do not forget to put the quotation marks around your plain text.
To complete the function, click the OK button.
In cell F4 a “Yes” or a “No” now appears, depending on whether or not the value in cell E4 is greater than the value in E17 .
You can read this as follows: Test to see if the value in cell E4 is greater than the value in cell E17. If it is, display “Yes” in cell F4; otherwise, display “No”.
If you want to use AutoFill to copy the function to cells F5 to F9, you must make the cell reference to cell E17 in the Logical_test argument an absolute reference ($E$17). This is necessary because every calculation in the conditional (Logical_test) argument must refer to $E$17, the average sales.
Results derived from the use of this function in a cell can be used just as the value in any cell is used. In other words, you can use cells containing such functions as arguments in another function. We will do that now…
In the current example, you would like to use excel IF functions in column G to conditionally calculate the bonuses. More particularly, if a cell in column F contains the text “Yes”, then you want a bonus, i.e., 2% of the total revenue, to appear in the corresponding cell in column G. Begin by clicking in G4. Then start the Function Wizard. Find and select the excel IF function. Now, in the Function Arguments dialog box, enter the three arguments as follows: The bonus is to be calculated if there is a “Yes” in cell F4. Therefore, enter F4=”Yes” in the Logical_test input box. This time you must type the quotation marks yourself. If the condition evaluates as true, a bonus is granted. Therefore type into the Value_if_true argument: 2%*E4. If the condition is not met, then display 0. Therefore, enter the number 0 (zero) as the Value_if_false argument. Finish by clicking OK.
Because the Logical_test argument you entered is not plain text but rather a formula, you must enter the quote signs around the “Yes” yourself. In this case, Excel will not add the quotes automatically.
This can be read as follows: If F4 contains a plain text Yes, calculate and display 2% of the value in E4 (that salesperson’s sales), otherwise display the number 0.