Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Rank
I have set up a formula to assign a ranking by both # and $ using the
following table: A B C 46 Angie 16 $34,676.31 47 Bettye 16 $9,908.81 48 Felicia 10 $6,971.93 49 Gayle 28 $27,937.91 50 Jim 8 $1,575.39 51 Judy 15 $23,055.00 52 Les 1 $75.00 53 Linda 8 $4,169.00 54 Manny 11 $6,380.82 55 Perlene 9 $1,630.00 56 Rachael 1 $150.00 57 Sandra 10 $4,255.00 58 Scott 6 $6,122.95 59 Wade 12 $6,636.37 In the event of a tie, such as between Angie and Bettye, is it possible to have Excel refer to the dollar amounts as a tie-breaker so-to-speak? |
#2
|
|||
|
|||
Assuming that Column B is ranked highest to lowest, and that a tie is
determined by the highest amount, try... D46, copied down: =RANK(B46,$B$46:$B$59)+IF(COUNTIF($B$46:$B$59,B46) ,MATCH(C46,LARGE(IF($B$ 46:$B$59=B46,$C$46:$C$59),ROW(INDIRECT("1:"&COUNTI F($B$46:$B$59,B46)))),0 )-1) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "StephanieH" wrote: I have set up a formula to assign a ranking by both # and $ using the following table: A B C 46 Angie 16 $34,676.31 47 Bettye 16 $9,908.81 48 Felicia 10 $6,971.93 49 Gayle 28 $27,937.91 50 Jim 8 $1,575.39 51 Judy 15 $23,055.00 52 Les 1 $75.00 53 Linda 8 $4,169.00 54 Manny 11 $6,380.82 55 Perlene 9 $1,630.00 56 Rachael 1 $150.00 57 Sandra 10 $4,255.00 58 Scott 6 $6,122.95 59 Wade 12 $6,636.37 In the event of a tie, such as between Angie and Bettye, is it possible to have Excel refer to the dollar amounts as a tie-breaker so-to-speak? |
#3
|
|||
|
|||
On Thu, 13 Oct 2005 09:02:08 -0700, "StephanieH"
wrote: I have set up a formula to assign a ranking by both # and $ using the following table: A B C 46 Angie 16 $34,676.31 47 Bettye 16 $9,908.81 48 Felicia 10 $6,971.93 49 Gayle 28 $27,937.91 50 Jim 8 $1,575.39 51 Judy 15 $23,055.00 52 Les 1 $75.00 53 Linda 8 $4,169.00 54 Manny 11 $6,380.82 55 Perlene 9 $1,630.00 56 Rachael 1 $150.00 57 Sandra 10 $4,255.00 58 Scott 6 $6,122.95 59 Wade 12 $6,636.37 In the event of a tie, such as between Angie and Bettye, is it possible to have Excel refer to the dollar amounts as a tie-breaker so-to-speak? It's not clear why there appear to be missing ranks, e.g. 2,3,4 etc. so I'm assuming this is just a subset of a larger ranking. There's probably a complex COUNTIF, SUMIF, SUMProduct formula coming along any moment from the experts here, but isn't one option simply to sort the data with the first sort being column B and the second sort column B (choose ascending/descending as appropriate). Then renumber the top to bottom accordingy then re-sort back into alphabetical order. HTH. __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANK formula | Excel Discussion (Misc queries) | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
rank question | Excel Discussion (Misc queries) | |||
Rank fx - Fill Down? | Excel Worksheet Functions | |||
Rank Function | Excel Worksheet Functions |