Ranker of Maximum Values on Changing Metric

excelgirl2583

New Member
Joined
Oct 19, 2015
Messages
4
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
customer osatproblems expereincedtimeliness
store 1752277
store 2673385
store 3894467
store 4911195

<TBODY>
</TBODY>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.

ABCDEFG
1top3No.customer osatStore (2007-)Store (2010+)
2hurdle84191store 4store 4
3count3289store 3store 3
4384store 7store 7
5
6
7Storecustomer osat
8store 175
9store 267
10store 389
11store 491
12store 575
13store 669
14store 784
15store 877
16store 966
17store 1055

<tbody>
</tbody>
Sheet24

Worksheet Formulas
CellFormula
D2=IF(ROWS(D$2:D2)<=$B$3,ROWS(D$2:D2),"")
E2=IF(D2="","",LARGE($B$8:$B$17,D2))
D3=IF(ROWS(D$2:D3)<=$B$3,ROWS(D$2:D3),"")
E3=IF(D3="","",LARGE($B$8:$B$17,D3))
D4=IF(ROWS(D$2:D4)<=$B$3,ROWS(D$2:D4),"")
E4=IF(D4="","",LARGE($B$8:$B$17,D4))
D5=IF(ROWS(D$2:D5)<=$B$3,ROWS(D$2:D5),"")
E5=IF(D5="","",LARGE($B$8:$B$17,D5))
D6=IF(ROWS(D$2:D6)<=$B$3,ROWS(D$2:D6),"")
E6=IF(D6="","",LARGE($B$8:$B$17,D6))
D7=IF(ROWS(D$2:D7)<=$B$3,ROWS(D$2:D7),"")
E7=IF(D7="","",LARGE($B$8:$B$17,D7))
D8=IF(ROWS(D$2:D8)<=$B$3,ROWS(D$2:D8),"")
E8=IF(D8="","",LARGE($B$8:$B$17,D8))
D9=IF(ROWS(D$2:D9)<=$B$3,ROWS(D$2:D9),"")
E9=IF(D9="","",LARGE($B$8:$B$17,D9))
G2=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))))
G3=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))))
G4=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))))
G5=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))))
G6=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))))
G7=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))))
G8=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))))
G9=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))))
B2=LARGE($B$8:$B$17,B1)
B3=COUNTIF(B8:B17,">="&B2)

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
F2{=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)),))}
F3{=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)),))}
F4{=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)),))}
F5{=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)),))}
F6{=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)),))}
F7{=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)),))}
F8{=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)),))}
F9{=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)),))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Last edited:
Upvote 0
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
 
Upvote 0
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.

IJKLMNO
1bottom5No.customer osatStore (2007-)Store (2010+)
2hurdle75155store 10store 10
3count6266store 9store 9
4367store 2store 2
5469store 6store 6
6575store 5store 5
7675store 1store 1
8
9

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
top X and ties

Worksheet Formulas
CellFormula
J2=SMALL($B$8:$B$17,J1)
J3=COUNTIF(B8:B17,"<="&J2)
L2=IF(ROWS(L$2:L2)<=$J$3,ROWS(L$2:L2),"")
M2=IF(L2="","",SMALL($B$8:$B$17,L2))
O2=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))))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
N2{=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)),))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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