Auto ranking in Excel

[doris_day]

I have a sheet on which I rank the prices in one of the columns but each race has different numbers of runners and so I have to manually alter the array size for the ranking to work correctly.

Is there a simple way I can design this so the ranking happens automatically, irrespective of the number of runners ?

Thanks for any help

[osknows]

Not sure why you need to adjust the sizing? The following ranks F6 against the range F5 to F55

=IF(F6="","",(RANK(F6,$F$5:$F$55,1)) allows equal ranking. (Eg if 2 prices are the same ranks will be equal)

=IF(F6="","",(RANK(F6,$F$5:$F$55,1)+COUNTIF($F$5:F6,F6)-1)) does not allow equal ranking so if 2 prices are the same one is ranked lower than the next in order they appear in excel

[doris_day]

Thanks. Shows my lack of Excel knowledge. I wasnt using an IF statement, just a Rank function. Its obvious what I should have done now you point it out.

Thanks for the help. Much appreciated.

[BERTRAND]

I thought that this formula I use to rank betfair spdata would not allow equal ranking, but alas , it does!

Code: Select all

=RANK(H2,INDIRECT(ADDRESS(MATCH(A2,$A$1:$A$65000,0),8,1)&":"&ADDRESS(MATCH(A2,$A$1:$A$65000,0)+COUNTIF($A$1:$A$65000,A2)-1,8,1)),1)

Bert

[alrodopial]

Mark prefers the below:

Code: Select all

=IF(F5=0, "", SUMPRODUCT((F5>$F$5:$F$55) * ($F$5:$F$55<>0)) +1+COUNTIF($F$5:F5,F5)-1)

[BERTRAND]

Hi Alrodopial

Interesting, I haven't seen that one before. But that is for a BA trigger sheet.

Bert

[osknows]

=RANK(H6,$H$5:$H$55,1)

is the same as

=RANK(H2,INDIRECT(ADDRESS(MATCH(A2,$A$1:$A$65000,0),8,1)&":"&ADDRESS(MATCH(A2,$A$1:$A$65000,0)+COUNTIF($A$1:$A$65000,A2)-1,8,1)),1)

For unique rank

=RANK(H6,$H$5:$H$55,1)+COUNTIF($H$5:H6,H6)-1)

is the same as

=RANK(H2,INDIRECT(ADDRESS(MATCH(A2,$A$1:$A$65000,0),8,1)&":"&ADDRESS(MATCH(A2,$A$1:$A$65000,0)+COUNTIF($A$1:$A$65000,A2)-1,8,1)),1)+COUNTIF(INDIRECT(ADDRESS(MATCH(A2,$A$1:$A$65000,0),8,1)&":"&ADDRESS(ROW(),8,1)),H2)-1

[alrodopial]

Ranking:

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

[osknows]

I think what Berts attempting is to rank rows within rows. Eg

group1 - 2
group1 - 3
group1 - 3
group1 - 5
group2 - 2
group2 - 4
group2 - 6
group2 - 5

so it would look like

1
2
3
4
1
2
4
3

Trying to do this with array formulae over large ranges is going to slow the calcs down. You could write a VBA function which would probably be quicker still

[osknows]

This is another alternative formula and should be much quicker calculation-wise

=RANK(H2,OFFSET($H$1,MATCH(A2,A:A,0)-1,0,COUNTIF(A:A,A2),1),1)+COUNTIF(OFFSET($H$1,MATCH(A2,A:A,0)-1,0,COUNTIF($A$1:A2,A2),1),H2)-1

[BERTRAND]

Many thanks Folks,
I keep wondering wether there is a tool ie flowchart to facilitate the "nesting" of so many functions, but I guess it's just familiarity with each function.
I usually have to switch off the auto save in Excel when I use functions such as this.
Bert