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?

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?

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
__________________________