5. In the range B12:E12, Benicio wants to display a rating depending on the total sales for each quarter. He listed the rating criteria in the range A14:F15. For example, if total sales in Quarter 1 are between $5900 and $5999, the Performance rating is Good. Enter the performance ratings as follows:
In cell B12, you will enter a formula to display the performance rating based on the total sales for Quarter 1. Similarly, you will use formulas in cells C12, D12, and E12 to display the performance ratings for Quarters 2, 3, and 4 respectively.
Here is an example of how the formulas should be entered:
- In cell B12, enter the formula:
=VLOOKUP(B9,A14:F15,2,TRUE)
- In cell C12, enter the formula:
=VLOOKUP(C9,A14:F15,2,TRUE)
- In cell D12, enter the formula:
=VLOOKUP(D9,A14:F15,2,TRUE)
- In cell E12, enter the formula:
=VLOOKUP(E9,A14:F15,2,TRUE)
These formulas will look up the total sales values from B9 to E9 and find the corresponding performance ratings from the range A14:F15. They will return the performance rating based on the criteria specified in the lookup table.