Generate a product sales discount total by category report in excel for each quarter starting 7/1/94 through 6/30/96, round your numbers to $000’s using a custom number format.
The results should look like this:
The discounts table uses an array function exactly the same as the sales, but instead of summing the sales defined name range, it sums the discount defined name range we just added to the raw dump in the prior step. The rank formula works similar to task 8 above. To get a huge jump on this task, you should copy/paste the entire sheet from the sales analysis and modify as needed (control click and drag the sheet tab and drop all the way to the right of the sheet tabs).
To get the total discounts for the top 3 categories, we use the SUMIF function (not an array function) to sum where rank is less than 4, e.g. the rough formula for B22 is: SUMIF B13 through B20 “<4” B3 through B10
The expert performance time for this task is 1 minute, 30 seconds. The performance test assumes you are starting from the query created in the prior task.