Top 5 Help.

[thepartydj]

I have an excel sheet that lists 35 sales reps. This lists there name and how many sales they have.

How Can I make my sheet so I have a top 5 list that will take the top five people of sales out of the list automatically and list them in the first 5 lines on the worksheet. I want this list to change automatically for each sale entered.

I have attached a picture of my workbook with most of my employees removed. The formula I am using now is just not working so I want to start from the start.

need more info let me know. Thanks

[mdmackillop]

In A16 =RANK(F16,F$16:F$50) and copy down
In B8 =VLOOKUP(A8,A$16:C$50,2,FALSE) and copy down
In C8 similar to B8
You can hide the rank using custom format ";;;"

[Bob Phillips]

=INDEX(B$16:B$26,MATCH(LARGE($F$16:$F$26-ROW($F$16:$F$26)/10^10,ROWS($B$1:B1)),$F$16:$F$26-ROW($F$16:$F$26)/10^10,0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.
When editing the formula, it must again be array-entered.

[thepartydj]

In A16 =RANK(F16,F$16:F$50) and copy down
In B8 =VLOOKUP(A8,A$16:C$50,2,FALSE) and copy down
In C8 similar to B8
You can hide the rank using custom format ";;;"

This would take care of the total ARPU but I also want the top 5 for sales also at the same time so I don't want to sort the main list just the two top 5's in the top.

[thepartydj]

=INDEX(B$16:B$26,MATCH(LARGE($F$16:$F$26-ROW($F$16:$F$26)/10^10,ROWS($B$1:B1)),$F$16:$F$26-ROW($F$16:$F$26)/10^10,0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.
When editing the formula, it must again be array-entered.

Were should this formula go? What cell?

[mdmackillop]

:clap2: It goes in B8 with adjusted code in the other columns. BTW, change 26 to 50 for 35 entries.

[thepartydj]

:clap2: It goes in B8 with adjusted code in the other columns. BTW, change 26 to 50 for 35 entries.

That worked great!!! Thanks

[mdmackillop]

Hi
If this is solved, can you mark it so using the option in the Thread Tools dropdown
Regards
MD