» 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:
Recommended Books:
- Rich Dad's Guide to Investing: What the Rich Invest in, That the Poor and the Middle Class Do Not!
- F1 Get the Most out of Excel! The Ultimate Excel tip Help Guide
- Financial Statement Analysis: A Practitioner's Guide, 3rd Edition
- Microsoft Excel 2002 Visual Basic for Applications Step by Step
- Good to Great: Why Some Companies Make the Leap... and Others Don't
- Marketing Management
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