The following activities are designed to introduce you to the use of the Excel logical function IF for phrasing decisions in your calculations. The exercises are designed for you to perform as you read, so access to Excel and the worksheet Example1 which we developed in Lessons 1-5 is highly desirable.
Suppose we wish to change the way in which we allocate the commission rate to each of our sales regions. In our previous example, one rate was used for all regions. Let's assume now that the commission rate is set each six months as an incentive for higher sales in the following way. If a region attains total sales of more than $100,000 the sales commission rate will be 6%. If the region attains total sales of more than $80,000, but less than or equal to $100,000, the rate will be 5%. If a region's total sales are below $80,000, the rate will be set to 3.5%.We wish to modify our worksheet to account for these new rules. We begin by setting aside cells for the three sales commission rates as shown in the following figure. Note in that figure that we have deleted the formulas used before for computing adjusted sales, because those formulas must be adjusted now for the new commission scheme.
Let's begin by constructing a formula for cell B15. We know we wish to subtract the commissions paid from the gross sales just as before. The difference is that the commission rate to be paid will in fact depend upon the gross sales. How can we phrase this unknown in our formula?We'd like our formula to "make a decision" about which commission rate to use based on the value of the total sales. The Excel IF function provides exactly this kind of capability. The IF function is structured as follows.
IF(condition, action or value if condition true, action or value if condition false)
For example, the hypothetical expression (this has nothing to do with our example):
IF(A2 > 10, 25, 4*A2)would assign the value 25 if the number in cell A2 is larger than 10 and assign the number 4 times the value in cell A2 if the value in A2 is not larger than 10.
With this in mind, we can begin the construction of our formula for cell B15. The first part of the formula is shown in the figure below. It computes the adjusted sales based on the rate in cell C18 whenever the total sales in cell B10 is larger than $100,000. We've left blank for the moment what happens otherwise.
Now let's consider what happens whenever the sales figure in B10 is not larger than $100,000. It depends again. If the figure is larger than $80,000, we use the rate in cell C19, otherwise we use the rate in cell C20. This calls for a second IF function inside the first IF function. The following figure shows the first part of this second IF function being entered.
Finally, if the sales amount is not larger than $80,000, we wish to use the rate in C20. This condition then finishes the formula off, as shown in the following figure.
Study the nested IF function in the previous figure carefully so that you understand how it represents the procedure for assigning a commission rate that we described earlier. Once you are confident of that understanding, replicate the formula to cells C15 and D15. We do not want to replicate to E15 as before (why not?). The following figure shows the results your worksheet should now contain. Find and correct any errors before proceeding.
To complete the worksheet calculations, we need to supply a formula for cell E15 to compute the grand total of adjusted sales. We do this by adding the three regional total adjusted sales. We employ the SUM function as shown below.Once the above formula is entered, you should have a worksheet containing the data shown below. Correct any errors you find before proceeding.
Let's return to the exercise you previously completed (Lesson 4) to compute the monthly payment for a car loan. In the solution to that problem, we assumed a fixed interest rate independent of the value of the car. Let's now suppose that your bank is running a special deal on car loans. For loans over $12,000, they will reduce the standard interest rate by 1% (to 9%).You are to modify your worksheet to account for this new arrangement. Of course an IF function will be just what's called for. Begin by identifying two possible interest rates as we show in the figure below.
Now modify the formula for computing the payment in cell D6 to account for the new arrangement. The portion of the formula highlighted in the previous figure must be changed. Consider using an IF function in this position to compute the appropriate interest rate based on the amount of the loan. The following figure lays out the new formula. Fill in the missing components.
Once you get the new formula defined in cell D6, replicate it to cells E6, F6, G6, and H6. Check your worksheet for the loan amount shown in the following figure. Find and correct any errors.
As a final check of your work, compare your worksheet to the one below for a loan amount of $14,000. Once again, find and correct any errors.
