Auto ranking in Excel

Please post any questions regarding the program here.

Moderator: 2020vision

Auto ranking in Excel

Postby doris_day » Sat Dec 12, 2009 12:31 pm

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
'He was looking for the card so high and wild he'd never need to deal another' - Leonard Cohen
User avatar
doris_day
 
Posts: 968
Joined: Fri Nov 02, 2007 12:34 am

Postby osknows » Sat Dec 12, 2009 1:11 pm

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
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby doris_day » Sat Dec 12, 2009 1:32 pm

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.
'He was looking for the card so high and wild he'd never need to deal another' - Leonard Cohen
User avatar
doris_day
 
Posts: 968
Joined: Fri Nov 02, 2007 12:34 am

Postby BERTRAND » Wed Feb 15, 2012 8:38 am

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
BERTRAND
 
Posts: 99
Joined: Thu Feb 03, 2011 4:15 pm

Postby alrodopial » Wed Feb 15, 2012 8:53 am

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)
alrodopial
 
Posts: 1383
Joined: Wed Dec 06, 2006 9:59 pm

Postby BERTRAND » Wed Feb 15, 2012 9:10 am

Hi Alrodopial

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

Bert
BERTRAND
 
Posts: 99
Joined: Thu Feb 03, 2011 4:15 pm

Postby osknows » Wed Feb 15, 2012 1:38 pm

=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
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby alrodopial » Wed Feb 15, 2012 1:43 pm

alrodopial
 
Posts: 1383
Joined: Wed Dec 06, 2006 9:59 pm

Postby osknows » Wed Feb 15, 2012 1:49 pm

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
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby osknows » Wed Feb 15, 2012 11:52 pm

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
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby BERTRAND » Thu Feb 16, 2012 2:05 pm

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
BERTRAND
 
Posts: 99
Joined: Thu Feb 03, 2011 4:15 pm


Return to Help

Who is online

Users browsing this forum: Google [Bot] and 24 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.