Calculate the Discount (8 points)
Enter a formula in cell B27 to calculate and display the discount that applies. A discount is not always applicable. If the projected number of customers for the entered class is less than the average number of customers for that class over the last 4 years, then the Special Discount is applicable and you should store the discount given in cell G19 in cell B27. Otherwise, the trip is not eligible for the discount and you should store 0 in cell B27. Confusing, huh? Let's take this a step at a time...
1.
Start by entering a formula in B27 that calculates the discount to be applied for the Premier Class. Your formula should not use the actual values in cells B17 and B18 but should instead refer to those cells. If the Class Num entered in cell B23 equals 1 and the projected value in cell B18 is less than the average value in cell B17 then the formula should return the discount value in cell G19, otherwise it should return 0.
2.
At this point, if either of the first pair of conditions is not met the formula returns 0. Instead what it should really do is to check if similar conditions are met for any of the other classes.
3.
Replace the 0 in the IF function, with another IF function that checks if the class num entered in cell B23 equals 2 and the projected value in cell C18 is less than the average value in cell C17. If both these conditions are satisfied, the nested IF should return the discount value in cell G19, otherwise it should return 0 (you might find it helpful to write this part in another cell first and copy the results to your original formula once you are sure it works correctly.).
4.
At this point, we are checking conditions for the first two classes of travel. Extend the formula further to handle all three classes of travel.
Bookmarks