RANK Issue in Excel

[conorm]

Hi, I'm trying to rank horses in order of their price and am currently using the formula

=RANK(B3, $B$3:$B$40,1)

But when there are multiple horses with the same price it gives them the same rank and this becomes and issue when turning this into a graph afterwards as some of the results are overlaid and lost, is there anyway around this?

Thanks in advance.

[ebasson61]

This is a bit klunky, but if you have the following numbers in cells A1 to A8:

5
4
4
2
8
8
7
6

which you want to rank. This would give you rankings of:

5
6
6
8
1
1
3
4

In cells B1 to B8, place the following:

=IF(COUNTIF($A$1:$A$8, A1)>1, (A1*100)+ROW(A1), A1*100)

This will provide a unique, albeit somewhat arbritrary separation of non-unique numbers. Then simply rank the B cells and not the A cells.

Hope this is clear and that it helps.

[conorm]

Thanks a lot but I can't seem to get this to work and there must be some simpler way of doing this than that?

[TheTub]

But when there are multiple horses with the same price it gives them the same rank and this becomes and issue

Think of the excel spreadsheet as a stupid, non-intelligent moron.

If there are multiple selections, say A & B at the same price then it will not know you want to rank them in a different way. Does it put 'A' first or 'B' first?

I would solve this by sorting into price order then place a count by each row. This will give you a sequential rank.

But the RANK function will count two at the same price as the same rank.

[conorm]

thanks

[staker72]

HI
The simplest solution is somewhere in the equations to add a very small no say 0.0000000001 and on each row have a similarly small but different no. As the odds are discrete that will mean 2 odds the same will show as very slightly different no's and excel will rank accordingly but the no. added is so tiny as to not make any difference to the outcome.

[ebasson61]

Staker, that's essentially what my proposed solution does. It works too, as I've tried it out.

[mickey]

Very good example of rank function and it's use, should help you.

http://www.cpearson.com/excel/Rank.aspx

[conorm]

Hi Mickey thanks that worked great, one last thing I'm using this formula from the link you gave me for unique ascending order.

=COUNT($I$3:$I$170)-(RANK(I3,$I$3:$I$170)+COUNTIF($I$3:I3,I3)-1)+1

But strangely, when I arrange the column using the ascending order it does everything normally expect it does all the unique numbers backwards. So 1 ,2, 3, 3, 3, 4, 5 ends up being ranked 1, 2 , 5, 4 ,3, 6, 7?

Why are computers so annoying?

[dropss81]

Try this formula

=SMALL(IF($E$4:$E$29=0, "", $E$4:$E$29), ROWS($A$1:A1))

E: Column is on your values
A Column is your Top Left corner sheet (Blank Cell)

Press Then Shift+Enter



Is anyone here to post an Automation using the Rank option.. Is a new feature and I'm unable to understand how to use it ..