Anybody any good with Array formulas?

[churmie]

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.

Category	Product	Service
Toys	Action man	99.10%
Toys	Barbie	88.75%
Garden	BBQ	98.40%
Toys	Beanie Bears	98.54%
Garden	Bench	89.75%
Games	Chess	99.64%
Games	Draughts	99.89%
Garden	Gas Cyclinder	99.52%
Games	Guess Who	24.70%
Garden	Lantern	74.90%
Garden	Lawn Mower	94.50%
Toys	Magic cards	98.64%
Games	Monopoly	84.70%
Toys	Pokemon	100.00%
Tools	Screwdriver	99.00%
Garden	Shed	98.50%
Tools	Spanner	98.80%
Games	Subbeteo	65.46%
Toys	Sylvanian Family	99.25%
Toys	Top Trumps	93.67%
Games	Trivial Pursuit	92.50%

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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?

Category	Toys
1	Sylvanian Family	99.25%
2	Action man	99.10%
3	Magic cards	98.64%
4	Beanie Bears	98.54%
5	Top Trumps	93.67%

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Many Thanks in advance!!!

 

[BGY23]

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.

 

[churmie]

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.

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.

 

[njimack]

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%.

 

[FormR]

Hi, here is a formula based option you can try - note the comment about pokemon in the prior post.

Excel 2013/2016
	A	B	C	D	E	F	G
1	Category	Product	Service		Toys
2	Toys	Action man	99%		1	Pokemon	100.00%
3	Toys	Barbie	89%		2	Sylvanian Family	99.25%
4	Garden	BBQ	98%		3	Action man	99.10%
5	Toys	Beanie Bears	99%		4	Magic cards	98.64%
6	Garden	Bench	90%		5	Beanie Bears	98.54%
7	Games	Chess	100%
8	Games	Draughts	100%
9	Garden	Gas Cyclinder	100%
10	Games	Guess Who	25%
11	Garden	Lantern	75%
12	Garden	Lawn Mower	95%
13	Toys	Magic cards	99%
14	Games	Monopoly	85%
15	Toys	Pokemon	100%
16	Tools	Screwdriver	99%
17	Garden	Shed	99%
18	Tools	Spanner	99%
19	Games	Subbeteo	65%
20	Toys	Sylvanian Family	99%
21	Toys	Top Trumps	94%
22	Games	Trivial Pursuit	93%
Sheet1

Cell Formulas
Range	Formula
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)

 

[churmie]

Thank You FormR, that is exactly what I was looking for!

 

[churmie]

How do I reverse it? The bottom 5? That formula is excellent

 

[Twollaston]

How do I reverse it? The bottom 5? That formula is excellent

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
	H	I	J	K	L	M	N
4	Category	Product	Service		Toys	Top 5
5	Toys	Pokemon	$ 100.0000		1	Pokemon	$ 100.00
6	Games	Draughts	$ 99.8900		2	Sylvanian Family	$ 99.25
7	Games	Chess	$ 99.6400		3	Action man	$ 99.10
8	Garden	Gas Cyclinder	$ 99.5200		4	Magic cards	$ 98.64
9	Toys	Sylvanian Family	$ 99.2500		5	Beanie Bears	$ 98.54
10	Toys	Action man	$ 99.1000
11	Tools	Screwdriver	$ 99.0000
12	Tools	Spanner	$ 98.8000
13	Toys	Magic cards	$ 98.6400
14	Toys	Beanie Bears	$ 98.5400
15	Garden	Shed	$ 98.5000		Toys	Bottom 5
16	Garden	BBQ	$ 98.4000		1	Barbie	$ 88.75
17	Garden	Lawn Mower	$ 94.5000		2	Top Trumps	$ 93.67
18	Toys	Top Trumps	$ 93.6700		3	Beanie Bears	$ 98.54
19	Games	Trivial Pursuit	$ 92.5000		4	Magic cards	$ 98.64
20	Garden	Bench	$ 89.7500		5	Action man	$ 99.10
21	Toys	Barbie	$ 88.7500
22	Games	Monopoly	$ 84.7000
23	Garden	Lantern	$ 74.9000
24	Games	Subbeteo	$ 65.4600
25	Games	Guess Who	#DIV/0!
Sheet5

Cell Formulas
Range	Formula
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

 

[FormR]

How do I reverse it? The bottom 5?

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

Worksheet Formulas Cell Formula 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(15,6,$C$2:$C$22/($A$2:$A$22=$E$1),E2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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

not sure if they will work if you have duplicate Service %'s because match only returns the first one