Ranker of Maximum Values on Changing Metric

[excelgirl2583]

I am sort of new to excel so this is probably an easy question but can't get my head around it! I am working on building a Dynamic Dashboard and want to people able to type in a metric (or pick from a drop down list) and have the dashboard show me the top 3 performers based on max (the names of store not the score)

And to make things more fun i would want it to identify that if "problems experienced" is picked as the metric on the dashboard it would show me the top 3 performers based on "MIN"

Thank you in advance! i have been banging my head against the wall trying to use index match and watching youtube videos with no luck
[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]customer osat[/TD]
[TD]problems expereinced[/TD]
[TD]timeliness[/TD]
[/TR]
[TR]
[TD]store 1[/TD]
[TD]75[/TD]
[TD]22[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]store 2[/TD]
[TD]67[/TD]
[TD]33[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]store 3[/TD]
[TD]89[/TD]
[TD]44[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]store 4[/TD]
[TD]91[/TD]
[TD]11[/TD]
[TD]95[/TD]
[/TR]
</TBODY>[/TABLE]

 

[DRSteele]

It's okay dear, lots of people watch the wrong things on youtube.

This might help you out: https://www.youtube.com/watch?v=rKDI-kdBsjY

I added some extra sample data to help audit the algorithm. You can copy downwards columns D through G to a number of rows sufficient to account for the predicted level of ties in your particular data set. You can copy the formulas to account for your fields "problems experienced" and "timeliness"; ensure you use LARGE instead of SMALL to reflect the appropriate ranking. And you can include any drop-down boxes you desire.

I included formulas for Excel 2007 and prior as well as formulas for Excel 2010 and subsequent.

	A	B	C	D	E	F	G
No.	customer osat	Store (2007-)	Store (2010+)
Store	customer osat
store 1
store 2
store 3
store 4
store 5
store 6
store 7
store 8
store 9
store 10

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #1F4E78"]top[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #1F4E78"]hurdle[/TD]
[TD="bgcolor: #E2EFDA, align: right"]84[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]
[TD="bgcolor: #E2EFDA, align: right"]91[/TD]
[TD="bgcolor: #E2EFDA"]store 4[/TD]
[TD="bgcolor: #E2EFDA"]store 4[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #1F4E78"]count[/TD]
[TD="bgcolor: #E2EFDA, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]2[/TD]
[TD="bgcolor: #E2EFDA, align: right"]89[/TD]
[TD="bgcolor: #E2EFDA"]store 3[/TD]
[TD="bgcolor: #E2EFDA"]store 3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]3[/TD]
[TD="bgcolor: #E2EFDA, align: right"]84[/TD]
[TD="bgcolor: #E2EFDA"]store 7[/TD]
[TD="bgcolor: #E2EFDA"]store 7[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]67[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]89[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]91[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]69[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]84[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet24​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=IF(ROWS(D$2:D2)<=$B$3,ROWS(D$2:D2),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(D2="","",LARGE($B$8:$B$17,D2))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=IF(ROWS(D$2:D3)<=$B$3,ROWS(D$2:D3),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=IF(D3="","",LARGE($B$8:$B$17,D3))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=IF(ROWS(D$2:D4)<=$B$3,ROWS(D$2:D4),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=IF(D4="","",LARGE($B$8:$B$17,D4))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]=IF(ROWS(D$2:D5)<=$B$3,ROWS(D$2:D5),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E5[/TH]
[TD="align: left"]=IF(D5="","",LARGE($B$8:$B$17,D5))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D6[/TH]
[TD="align: left"]=IF(ROWS(D$2:D6)<=$B$3,ROWS(D$2:D6),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E6[/TH]
[TD="align: left"]=IF(D6="","",LARGE($B$8:$B$17,D6))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D7[/TH]
[TD="align: left"]=IF(ROWS(D$2:D7)<=$B$3,ROWS(D$2:D7),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E7[/TH]
[TD="align: left"]=IF(D7="","",LARGE($B$8:$B$17,D7))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D8[/TH]
[TD="align: left"]=IF(ROWS(D$2:D8)<=$B$3,ROWS(D$2:D8),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E8[/TH]
[TD="align: left"]=IF(D8="","",LARGE($B$8:$B$17,D8))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D9[/TH]
[TD="align: left"]=IF(ROWS(D$2:D9)<=$B$3,ROWS(D$2:D9),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E9[/TH]
[TD="align: left"]=IF(D9="","",LARGE($B$8:$B$17,D9))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=IF(D2="","",INDEX($A$8:$A$17,AGGREGATE(15,6,(ROW($A$8:$A$17)-ROW($A$8)+1)/($B$8:$B$17=E2),COUNTIF($E$2:E2,E2))))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]=IF(D3="","",INDEX($A$8:$A$17,AGGREGATE(15,6,(ROW($A$8:$A$17)-ROW($A$8)+1)/($B$8:$B$17=E3),COUNTIF($E$2:E3,E3))))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"]=IF(D4="","",INDEX($A$8:$A$17,AGGREGATE(15,6,(ROW($A$8:$A$17)-ROW($A$8)+1)/($B$8:$B$17=E4),COUNTIF($E$2:E4,E4))))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G5[/TH]
[TD="align: left"]=IF(D5="","",INDEX($A$8:$A$17,AGGREGATE(15,6,(ROW($A$8:$A$17)-ROW($A$8)+1)/($B$8:$B$17=E5),COUNTIF($E$2:E5,E5))))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G6[/TH]
[TD="align: left"]=IF(D6="","",INDEX($A$8:$A$17,AGGREGATE(15,6,(ROW($A$8:$A$17)-ROW($A$8)+1)/($B$8:$B$17=E6),COUNTIF($E$2:E6,E6))))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G7[/TH]
[TD="align: left"]=IF(D7="","",INDEX($A$8:$A$17,AGGREGATE(15,6,(ROW($A$8:$A$17)-ROW($A$8)+1)/($B$8:$B$17=E7),COUNTIF($E$2:E7,E7))))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G8[/TH]
[TD="align: left"]=IF(D8="","",INDEX($A$8:$A$17,AGGREGATE(15,6,(ROW($A$8:$A$17)-ROW($A$8)+1)/($B$8:$B$17=E8),COUNTIF($E$2:E8,E8))))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G9[/TH]
[TD="align: left"]=IF(D9="","",INDEX($A$8:$A$17,AGGREGATE(15,6,(ROW($A$8:$A$17)-ROW($A$8)+1)/($B$8:$B$17=E9),COUNTIF($E$2:E9,E9))))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=LARGE($B$8:$B$17,B1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=COUNTIF(B8:B17,">="&B2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=IF(D2="","",INDEX($A$8:$A$17,SMALL(IF($B$8:$B$17=E2,ROW($A$8:$A$17)-ROW($A$8)+1),COUNTIF($E$2:E2,E2)),))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]{=IF(D3="","",INDEX($A$8:$A$17,SMALL(IF($B$8:$B$17=E3,ROW($A$8:$A$17)-ROW($A$8)+1),COUNTIF($E$2:E3,E3)),))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]{=IF(D4="","",INDEX($A$8:$A$17,SMALL(IF($B$8:$B$17=E4,ROW($A$8:$A$17)-ROW($A$8)+1),COUNTIF($E$2:E4,E4)),))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F5[/TH]
[TD="align: left"]{=IF(D5="","",INDEX($A$8:$A$17,SMALL(IF($B$8:$B$17=E5,ROW($A$8:$A$17)-ROW($A$8)+1),COUNTIF($E$2:E5,E5)),))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F6[/TH]
[TD="align: left"]{=IF(D6="","",INDEX($A$8:$A$17,SMALL(IF($B$8:$B$17=E6,ROW($A$8:$A$17)-ROW($A$8)+1),COUNTIF($E$2:E6,E6)),))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F7[/TH]
[TD="align: left"]{=IF(D7="","",INDEX($A$8:$A$17,SMALL(IF($B$8:$B$17=E7,ROW($A$8:$A$17)-ROW($A$8)+1),COUNTIF($E$2:E7,E7)),))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F8[/TH]
[TD="align: left"]{=IF(D8="","",INDEX($A$8:$A$17,SMALL(IF($B$8:$B$17=E8,ROW($A$8:$A$17)-ROW($A$8)+1),COUNTIF($E$2:E8,E8)),))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F9[/TH]
[TD="align: left"]{=IF(D9="","",INDEX($A$8:$A$17,SMALL(IF($B$8:$B$17=E9,ROW($A$8:$A$17)-ROW($A$8)+1),COUNTIF($E$2:E9,E9)),))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[excelgirl2583]

It worked great!! Thanks you so much for your help and your clear description! I can't wait to know enough to reply to other people

 

[DRSteele]

You're welcome. I also created the inverse, where you might wish to find the bottom 3 or 5 or whatever as you would for your other two fields. Using my sample data, here it is. Copy the formulas in L2 to O2 down as far as necessary.

	I	J	K	L	M	N	O
No.	customer osat	Store (2007-)	Store (2010+)

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFD966"]bottom[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFD966"]hurdle[/TD]
[TD="bgcolor: #E2EFDA, align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]
[TD="bgcolor: #E2EFDA, align: right"]55[/TD]
[TD="bgcolor: #E2EFDA"]store 10[/TD]
[TD="bgcolor: #E2EFDA"]store 10[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFD966"]count[/TD]
[TD="bgcolor: #E2EFDA, align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]2[/TD]
[TD="bgcolor: #E2EFDA, align: right"]66[/TD]
[TD="bgcolor: #E2EFDA"]store 9[/TD]
[TD="bgcolor: #E2EFDA"]store 9[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]3[/TD]
[TD="bgcolor: #E2EFDA, align: right"]67[/TD]
[TD="bgcolor: #E2EFDA"]store 2[/TD]
[TD="bgcolor: #E2EFDA"]store 2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]4[/TD]
[TD="bgcolor: #E2EFDA, align: right"]69[/TD]
[TD="bgcolor: #E2EFDA"]store 6[/TD]
[TD="bgcolor: #E2EFDA"]store 6[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]5[/TD]
[TD="bgcolor: #E2EFDA, align: right"]75[/TD]
[TD="bgcolor: #E2EFDA"]store 5[/TD]
[TD="bgcolor: #E2EFDA"]store 5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]6[/TD]
[TD="bgcolor: #E2EFDA, align: right"]75[/TD]
[TD="bgcolor: #E2EFDA"]store 1[/TD]
[TD="bgcolor: #E2EFDA"]store 1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

</tbody>

top X and ties​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]=SMALL($B$8:$B$17,J1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J3[/TH]
[TD="align: left"]=COUNTIF(B8:B17,"<="&J2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]L2[/TH]
[TD="align: left"]=IF(ROWS(L$2:L2)<=$J$3,ROWS(L$2:L2),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]M2[/TH]
[TD="align: left"]=IF(L2="","",SMALL($B$8:$B$17,L2))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]O2[/TH]
[TD="align: left"]=IF(L2="","",INDEX($A$8:$A$17,AGGREGATE(14,6,(ROW($A$8:$A$17)-ROW($A$8)+1)/($B$8:$B$17=M2),COUNTIF(M$2:M2,M2))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]N2[/TH]
[TD="align: left"]{=IF(L2="","",INDEX($A$8:$A$17,LARGE(IF($B$8:$B$17=M2,ROW($A$8:$A$17)-ROW($A$8)+1),COUNTIF(M$2:M2,M2)),))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]