Problem:
Finding the name matching each rank in according to the total number of sales, listed per each contestant in Range A1:B6
Solution:
Use the LARGE function to find the sales matching each rank in column A.
Then, use the INDEX and MATCH functions to retrieve the name matching each total.
Following is the formula:
=INDEX($A$3:$A$7,MATCH(LARGE($B$3:$B$7,A12),$B$3:$B$7,0))
Example:
Range1
Name______Total
Mike______500
Donna_____1000
David_____700
Mischelle_300
John______1200
Range2
Rank____Name
1_______John
2_______Donna
3_______David
The formula shows the ranges as running from row 3 to row 7, while the data are in rows 2 to 6.
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
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?
Hi jwellings,
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:Originally Posted by jwellings
=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.
To help us help you, try to do the following:
1) Be precise about what you want to do, and provide a sample of your data / inputs - exactly as they are.
2) State the formula(e) / code that you have tried. People are happy to help , but if you haven't even given it a go, you are less likely to get help, or the help you get will be very basic.
3) State the results you are getting from your formula(e) / code already.
4) State the outputs that you *want* to be getting.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks