Top 20% Array Formula not calculating correctly

[mf1]

Hi everyone,

I have an array formula to calculate the Top 20% of each category in this data set and it's not calculating correctly. What it should be doing:

1. Select the Top 20% Stores of a selected category based on GMROF (sorted highest) , then:

• Calculate the average sales $ of the selected category from the Top 20%
• Calculate the average GP$ of the selected category from the Top 20%
• Calculate the average m2 of the selected category from the Top 20%
• Calculate the average stock $ of the selected category from the Top 20%

So the formula needs to go through the same calculation for the next category, as the Top 20% Stores for the next category maybe different from the previous category.

Thanks for your help

Cheers,
Narof

 

[AlanSidman]

You can do this with Pivot Tables and slicers.

 

[p45cal]

Taking Alan's suggestion a bit further, see the pivot table and slicer around row 2535 of the attached.
I think all of your requirements are met, but be aware that the averages are to be found in the bottom (sub)total row. The values above those averages are a bit meaningless since they're all just one store.
If you're not interest in seeing just which stores are in the top 20%, you can collapse the Dept Lvl 3/Category field to leave only the subtotal row.

 

[mf1]

Thanks for the reply.
I want to have another report linked to this so it can bring across all Top 20% data for every category. Would that mean I would have to have 30 pivot tables?

My original formula was trying to give me the answer for every category.

Thanks,

Michael

 

[p45cal]

On the slicer, click where the red arrow's pointing:

On the slicer, you can use the standard keyboard/mouse combinations (holding the shift or Ctrl key while selecting with the mouse) to select any combination of categories.

Or you can use the drop down:


Then depending on how much data you want to see on each category you can right-click anywhere in the Dept Lvl 3 column and choose to expand/collapse the entire field (see below). Or do it on a single category by clicking on the +/- buttons, visible on the extreme left below: