Anybody any good with Array formulas?

C

churmie

New Member
Joined
Jan 9, 2014
Messages
44
Hi

I am trying to create a dashboard that gives me the top 5 (and bottom 5) on a number of key metrics. I have put an example table below, but I trying to create a result that will list in order 1-5.

Please assume Category is column A, Product is Column B, etc.

[TABLE="width: 231"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Category[/TD]
[TD] Product[/TD]
[TD]Service[/TD]
[/TR]
[TR]
[TD]Toys[/TD]
[TD]Action man[/TD]
[TD]99.10%[/TD]
[/TR]
[TR]
[TD]Toys[/TD]
[TD]Barbie[/TD]
[TD]88.75%[/TD]
[/TR]
[TR]
[TD]Garden[/TD]
[TD]BBQ[/TD]
[TD]98.40%[/TD]
[/TR]
[TR]
[TD]Toys[/TD]
[TD]Beanie Bears[/TD]
[TD]98.54%[/TD]
[/TR]
[TR]
[TD]Garden[/TD]
[TD]Bench[/TD]
[TD]89.75%[/TD]
[/TR]
[TR]
[TD]Games[/TD]
[TD]Chess[/TD]
[TD]99.64%[/TD]
[/TR]
[TR]
[TD]Games[/TD]
[TD]Draughts[/TD]
[TD]99.89%[/TD]
[/TR]
[TR]
[TD]Garden[/TD]
[TD]Gas Cyclinder[/TD]
[TD]99.52%[/TD]
[/TR]
[TR]
[TD]Games[/TD]
[TD]Guess Who[/TD]
[TD]24.70%[/TD]
[/TR]
[TR]
[TD]Garden[/TD]
[TD]Lantern[/TD]
[TD]74.90%[/TD]
[/TR]
[TR]
[TD]Garden[/TD]
[TD]Lawn Mower[/TD]
[TD]94.50%[/TD]
[/TR]
[TR]
[TD]Toys[/TD]
[TD]Magic cards[/TD]
[TD]98.64%[/TD]
[/TR]
[TR]
[TD]Games[/TD]
[TD]Monopoly[/TD]
[TD]84.70%[/TD]
[/TR]
[TR]
[TD]Toys[/TD]
[TD]Pokemon[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]Tools[/TD]
[TD]Screwdriver[/TD]
[TD]99.00%[/TD]
[/TR]
[TR]
[TD]Garden[/TD]
[TD]Shed[/TD]
[TD]98.50%[/TD]
[/TR]
[TR]
[TD]Tools[/TD]
[TD]Spanner[/TD]
[TD]98.80%[/TD]
[/TR]
[TR]
[TD]Games[/TD]
[TD]Subbeteo[/TD]
[TD]65.46%[/TD]
[/TR]
[TR]
[TD]Toys[/TD]
[TD]Sylvanian Family[/TD]
[TD]99.25%[/TD]
[/TR]
[TR]
[TD]Toys[/TD]
[TD]Top Trumps[/TD]
[TD]93.67%[/TD]
[/TR]
[TR]
[TD]Games[/TD]
[TD]Trivial Pursuit[/TD]
[TD]92.50%[/TD]
[/TR]
</tbody>[/TABLE]

How could I get the following result? If every time I typed in Toys in the cell adjacent to the Category call below, so it would list the below?

[TABLE="width: 231"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Category[/TD]
[TD]Toys[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Sylvanian Family[/TD]
[TD]99.25%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Action man[/TD]
[TD]99.10%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Magic cards[/TD]
[TD]98.64%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Beanie Bears[/TD]
[TD]98.54%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Top Trumps[/TD]
[TD]93.67%[/TD]
[/TR]
</tbody>[/TABLE]

Many Thanks in advance!!!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
You can do this with a pivot table. Put the service in the Values area, the product in the rows area and the category in the filters area.
Click in the pivot table and select filter click top 10 and change to 5 items based upon service. Now click on sort and change to largest to smallest.
 
Upvote 0
BGY23 said:
You can do this with a pivot table. Put the service in the Values area, the product in the rows area and the category in the filters area.
Click in the pivot table and select filter click top 10 and change to 5 items based upon service. Now click on sort and change to largest to smallest.
Click to expand...

Hi, yeah I know a pivot would be easy, but I am trying to create a formula, as it is in the form of a dashboard, where I type in a category and everything changes for multiple measures. It needs to be easier and smoother than changing the selection in each pivot.
 
Upvote 0
I'd use a pivot table with Category as the filter, sort the service column Largest to Smallest and apply a Value filter to the Product column to display Top (or Bottom) 5.

Your example is wrong though, the first entry should be Pokémon with 100%.
 
Upvote 0
Hi, here is a formula based option you can try - note the comment about pokemon in the prior post.


Excel 2013/2016
ABCDEFG
1CategoryProductServiceToys
2ToysAction man99%1Pokemon100.00%
3ToysBarbie89%2Sylvanian Family99.25%
4GardenBBQ98%3Action man99.10%
5ToysBeanie Bears99%4Magic cards98.64%
6GardenBench90%5Beanie Bears98.54%
7GamesChess100%
8GamesDraughts100%
9GardenGas Cyclinder100%
10GamesGuess Who25%
11GardenLantern75%
12GardenLawn Mower95%
13ToysMagic cards99%
14GamesMonopoly85%
15ToysPokemon100%
16ToolsScrewdriver99%
17GardenShed99%
18ToolsSpanner99%
19GamesSubbeteo65%
20ToysSylvanian Family99%
21ToysTop Trumps94%
22GamesTrivial Pursuit93%
Sheet1
Cell Formulas
RangeFormula
F2=INDEX($B$2:$B$22,AGGREGATE(15,6,(ROW($B$2:$B$22)-MIN(ROW($B$2:$B$22))+1)/($C$2:$C$22=G2),COUNTIFS($G$2:$G2,G2)))
G2=AGGREGATE(14,6,$C$2:$C$22/($A$2:$A$22=$E$1),E2)
 
Upvote 0
churmie said:
How do I reverse it? The bottom 5? That formula is excellent
Click to expand...

You can try this (Basically you just want to put it in the aggregate function to look at Small instead of Large e.g. 14 to 15 the first argument in the formula.



Excel 2010
HIJKLMN
4CategoryProductServiceToysTop 5
5ToysPokemon$ 100.00001Pokemon$ 100.00
6GamesDraughts$ 99.89002Sylvanian Family$ 99.25
7GamesChess$ 99.64003Action man$ 99.10
8GardenGas Cyclinder$ 99.52004Magic cards$ 98.64
9ToysSylvanian Family$ 99.25005Beanie Bears$ 98.54
10ToysAction man$ 99.1000
11ToolsScrewdriver$ 99.0000
12ToolsSpanner$ 98.8000
13ToysMagic cards$ 98.6400
14ToysBeanie Bears$ 98.5400
15GardenShed$ 98.5000ToysBottom 5
16GardenBBQ$ 98.40001Barbie$ 88.75
17GardenLawn Mower$ 94.50002Top Trumps$ 93.67
18ToysTop Trumps$ 93.67003Beanie Bears$ 98.54
19GamesTrivial Pursuit$ 92.50004Magic cards$ 98.64
20GardenBench$ 89.75005Action man$ 99.10
21ToysBarbie$ 88.7500
22GamesMonopoly$ 84.7000
23GardenLantern$ 74.9000
24GamesSubbeteo$ 65.4600
25GamesGuess Who#DIV/0!
Sheet5
Cell Formulas
RangeFormula
M5=INDEX($I$5:$I$25,MATCH($N5,$J$5:$J$25,0))
M16=INDEX($I$5:$I$25,MATCH($N16,$J$5:$J$25,0))
N5=AGGREGATE(14,6,$J$5:$J$25/($L$4=$H$5:$H$25),$L5)
N16=AGGREGATE(15,6,$J$5:$J$25/($L$4=$H$5:$H$25),$L16)


NOTE: the names of the top and bottom 5 I'm not sure if they will work if you have duplicate Service %'s because match only returns the first one
 
Last edited:
Upvote 0
churmie said:
How do I reverse it? The bottom 5?
Click to expand...

Hi, as Twollaston eluded to, you just need to change the formula in G2 to:

FormR said:
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD]=INDEX($B$2:$B$22,AGGREGATE(15,6,(ROW($B$2:$B$22)-MIN(ROW($B$2:$B$22))+1)/($C$2:$C$22=G2),COUNTIFS($G$2:$G2,G2)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD]=AGGREGATE(15,6,$C$2:$C$22/($A$2:$A$22=$E$1),E2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

The formula in F2 stays as is and doesn't suffer the drawback quoted below.

Twollaston said:
not sure if they will work if you have duplicate Service %'s because match only returns the first one
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top