Categorize the discounts using the Northwest Trading ranges and generate a report of the total discounts given in the “markdown” category and compute the year over year change for last quarter.

The result should look like this:

We see from this that markdowns jumped a huge 557% last quarter over the same quarter the year before. This is what the CEO gut feel was telling her. Your analysis has now confirmed the problem.

To accomplish this task, you will need to use a lookup to categorize the raw data coming from the query. You should create a new sheet in excel labeled “lookups”. On that sheet enter the following:

0% none
5% low
10% medium
20% markdown

Select the region and name it “discountcategory”.

On the raw data dump sheet, copy/paste the new query results that include the discount percentage from the prior task.

Add a label for Column F called “DiscountCategory” and insert the following formula into F2: =VLOOKUP(E2,discountcategory,2,TRUE)

This will categorize each transaction as a none, low, medium, or markdown depending on where the discount % falls in our table. We configured the VLOOKUP function to use the range option to map to the business rules: 5-9% is low; 10-19% is medium; and any sale with a 20% or higher discount is considered a markdown.

Copy/paste this formula down for all the rows. To speed trick here is to copy the cell, then go left arrow to the E column, then ctl-downarrow to jump to the bottom of the list, then right arrow to get back into the F column, then ctl-shift-uparrow to highlight all the rows in the table, then ctl-v to paste the formula. You will not be able to meet the expert time on this task until you learn this shortcut.

With the data selected, name it ‘discount’.

Next we need to create another table that sums only the markdown discounts (a subcategory of the data we found in task 10.
Similar to task 10, get a jump on this by copy/pasting the entire sheet.

Now we need to extend our array function to only sum when the discount is categorized as “markdown”, here’s the formula for B3: {=SUM(IF(orderdate >= B$1,IF(orderdate <= B$2,IF(category = $A3,IF(discount = “markdown”, discountsale, 0), 0), 0), 0))}

Sum each quarter and add year over year percent changes.

The expert performance time for this task is 2 minute, 30 seconds. The performance test assumes you are starting from the query created in the prior task.