» Ranking Salespeople According To Sales Figure
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Range A1:B6 contains salesperson's names and their total number of sales.
We want to rank the top 3 salespeople according to their sales performance.
Solution:
Use the INDEX, MATCH, and LARGE functions as shown in the following formula:
=INDEX($A$2:$A$6,MATCH(LARGE($B$2:$B$6,A9),$B$2:$B$6,0))
Example:
Range1
Name______Total
Mike______500
Donna_____1000
David_____700
Mischelle_300
John______1200
Range2
Rank____Name
1_______John
2_______Donna
3_______David
Book Store:
Does not work if values are of equal ranking
jwellings
If two people have an identical sales value it repeats the first name twice, in positions 1 and 2.
Eg.
If sales are
Mike 1000
Donna 1000
Bill 900
Fred 800
The rankings shown are
1 Mike
2 Mike
3 Bill
ie. Donna is not shown.
This formula is almost what I need. Any ideas for a fix?
Reply: jwellings
Alan
Hi jwellings,
[QUOTE=jwellings]If two people have an identical sales value it repeats the first name twice, in positions 1 and 2.
Eg.
If sales are
Mike 1000
Donna 1000
Bill 900
Fred 800
The rankings shown are
1 Mike
2 Mike
3 Bill
ie. Donna is not shown.
This formula is almost what I need. Any ideas for a fix?[/QUOTE]You need to create a unique rank number like this (assume your date is in A1:B4) then enter the following in, say, C1 and copy down to C4:
=RANK(B1,B$1:B$4)+COUNTIF(B$1:B1,B1)-1
Then use that unique rank to pull out the names using INDEX or whatever you want to do.
HTH,
Alan.
Also, the "LARGE" reference cell should be A9, not A12.
The formula should read:
=INDEX($A$2:$A$6,MATCH(LARGE($B$2:$B$6,A9),$B$2:$B$6,0))
Finally, that formula must be copied into each of the "Result" cells to see the person so ranked. I.e., copy it into cells B10, B11, etc.
Cordially,
Russ