General Betting
There is currently 1 person viewing this thread.
I want to rank the runners in a race by their forecast odds but not in a conventional manner.
E.g. Horse A 4/1, Horse B 11/2, Horse C 11/2, Horse D 13/2, Horse E 8/1, Horse F 8/1, Horse G 10/1.
In the above example one would expect to see Horse A = Rank 1, Horses B & C = Joint Rank 2, Horse D = Rank 4, Horses E & F = Joint Rank 5 and Horse G = Rank 7.
I would like to rank the horses by their odds so that in the above example Horse A = Rank 1, Horses B & C = Rank 2, Horse D = Rank 3, Horses E & F = Rank 4 and Horse G= Rank 5.
I've shown below three examples from today's UK racing where I've manually added my desired Forecast Ranking figure in the rightmost column. It would speed things up enormously for me if someone could come up with a formula that could generate the numbers I'm looking for.
Thanks in advance.
E.g. Horse A 4/1, Horse B 11/2, Horse C 11/2, Horse D 13/2, Horse E 8/1, Horse F 8/1, Horse G 10/1.
In the above example one would expect to see Horse A = Rank 1, Horses B & C = Joint Rank 2, Horse D = Rank 4, Horses E & F = Joint Rank 5 and Horse G = Rank 7.
I would like to rank the horses by their odds so that in the above example Horse A = Rank 1, Horses B & C = Rank 2, Horse D = Rank 3, Horses E & F = Rank 4 and Horse G= Rank 5.
I've shown below three examples from today's UK racing where I've manually added my desired Forecast Ranking figure in the rightmost column. It would speed things up enormously for me if someone could come up with a formula that could generate the numbers I'm looking for.
Thanks in advance.
| A | B | C | D | E | F | G | H | |
| 1 | Time | Course | Horse | Forecast | Forecast odds | DSLR | Formline | My forecast ranking |
| 2 | 15:55 | Carlisle | Sheriff Hutton (IRE) | Fav | 4 | 37 | 6144P | 1 |
| 3 | 15:55 | Carlisle | Go Silver Bullet (FR) | Non Fav | 5 | 44 | 46525 | 2 |
| 4 | 15:55 | Carlisle | Magic Sky (FR) | Non Fav | 5 | 13 | PF481 | 2 |
| 5 | 15:55 | Carlisle | Checkerboard (IRE) | Non Fav | 6 | 6 | P196P | 3 |
| 6 | 15:55 | Carlisle | Dark Ben (FR) | Non Fav | 7 | 59 | P2413 | 4 |
| 7 | 15:55 | Carlisle | Storymaker | Non Fav | 8 | 28 | 78603 | 5 |
| 8 | 15:55 | Carlisle | Bay Cherry (IRE) | Non Fav | 8 | 27 | 32RF6 | 5 |
| 9 | 15:55 | Carlisle | Leac An Scail (IRE) | Non Fav | 9 | 43 | F7526 | 6 |
| 10 | 15:55 | Carlisle | Acrai Rua (IRE) | Non Fav | 14 | 24 | 1P3P2 | 7 |
| 11 | ||||||||
| 12 | 16:30 | Carlisle | Final Veto | Fav | 2 | 27 | 8PU31 | 1 |
| 13 | 16:30 | Carlisle | Nicky Tam (IRE) | Non Fav | 2.25 | 12 | 47431 | 2 |
| 14 | 16:30 | Carlisle | Sierra Victor (IRE) | Non Fav | 5.5 | 162 | P21P7 | 3 |
| 15 | 16:30 | Carlisle | Dollar Mick (IRE) | Non Fav | 5.5 | 22 | 65655 | 3 |
| 16 | 16:30 | Carlisle | Frontier Boy (IRE) | Non Fav | 9 | 70 | 60855 | 4 |
| 17 | 16:30 | Carlisle | Conflictofinterest | Non Fav | 12 | 30 | 27545 | 5 |
| 18 | 16:30 | Carlisle | Craicneasy (IRE) | Non Fav | 25 | 30 | 8P6P3 | 6 |
| 19 | ||||||||
| 20 | 15:10 | Sandown | Baby Run (FR) | J-Fav | 6 | 16 | U11U1 | 1 |
| 21 | 15:10 | Sandown | Balthazar King (IRE) | J-Fav | 6 | 10 | 42511 | 1 |
| 22 | 15:10 | Sandown | Aimigayle | Non Fav | 7 | 37 | 042P2 | 2 |
| 23 | 15:10 | Sandown | Meanus Dandy (IRE) | Non Fav | 10 | 49 | P41U7 | 3 |
| 24 | 15:10 | Sandown | Gentle Ranger (IRE) | Non Fav | 11 | 35 | F1PP4 | 4 |
| 25 | 15:10 | Sandown | Poker De Sivola (FR) | Non Fav | 12 | 7 | 1P76U | 5 |
| 26 | 15:10 | Sandown | Triggerman | Non Fav | 12 | 9 | FF3R1 | 5 |
| 27 | 15:10 | Sandown | Maktu | Non Fav | 14 | 28 | 25872 | 6 |
| 28 | 15:10 | Sandown | Cant Buy Time (IRE) | Non Fav | 14 | 14 | 8P5PF | 6 |
| 29 | 15:10 | Sandown | Faasel (IRE) | Non Fav | 16 | 37 | 72P02 | 7 |
| 30 | 15:10 | Sandown | Church Island (IRE) | Non Fav | 20 | 9 | 100 | 8 |
| 31 | 15:10 | Sandown | Exmoor Ranger (IRE) | Non Fav | 20 | 39 | 439U6 | 8 |
| 32 | 15:10 | Sandown | Fire And Rain (FR) | Non Fav | 25 | 27 | 30175 | 9 |
| 33 | 15:10 | Sandown | Major Malarkey (IRE) | Non Fav | 25 | 38 | 131PU | 9 |
| 34 | 15:10 | Sandown | Briery Fox (IRE) | Non Fav | 33 | 28 | 12647 | 10 |
| 35 | 15:10 | Sandown | Richards Sundance (IRE) | Non Fav | 33 | 37 | 392PU | 10 |
| 36 | 15:10 | Sandown | Silver Adonis (IRE) | Non Fav | 33 | 16 | P36U7 | 10 |
| 37 | 15:10 | Sandown | Iris De Balme (FR) | Non Fav | 66 | 7 | 450PP | 11 |
sort by:
Show
per page
Replies: 11
|
By:
If it is excelesqe than you highlight the column you want to sort then find sort on the dropdown menu and follow it from there.
|
|
By:
Cheers Alex but even with my limited knowledge of excel/open office calc, I realise that your suggestion won't have the desired effect.
Thanks anyway. |
|
By:
I tend to save as and then make liberal use of the undo button when attempting new things on spreadsheets. I am sure you can crack it.
|
|
By:
Hi t2r, I think using the RANK function in column H may be close to what you want, this gives duplicate numbers for the same rank, but subsequent numbers would increase so Checkerboard in your 1st race would be 4 not 3
|
|
By:
DickyM
Thanks for your reply. You're right about the RANK function but I need a means of ensuring those 'subsequent numbers' only increase in increments of 1. |
|
By:
Hi top2rated
You could do this if your prices in column E are sorted, lowest to highest ~ In cell H2 enter 1 In cell H3 enter =IF(E2=E3;H1;H1+1) Fill down H3 for the rest. When you get to your next race just copy H2 and H3 and fill down from the second pasted value etc... |
|
By:
Leave row 1 blank. In cells A2 to A8 (or whatever) put your forecast odds in ascending order. Then in cells B2 to B8 (or whatever) put the following formula, =IF(A2>A1,B1+1,B1).
|
|
By:
Thanks for the advice Compound Magic - I was hoping you'd see the thread!
 Just off to watch some football but I'll give your suggestion a go after the final whistle. All the best. |
|
By:
Ooops. Sorry Compound Magic, I've just noticed you got there first!
|
|
By:
Back again with improved formula ~
=IF(ISBLANK(E2);"";IF(OR(ISBLANK(H1);ISTEXT(H1));1;IF(E1=E2;H1;H1+1))) That formula in cell H2 and filled down for all races Cheers. |
|
By:
dogrelata
Sorry, I didn't see your suggestion - it appears we both submitted posts at 14:56; directly after which I logged out. I tried your solution first but the shortest priced runner in the second race was returning the same ranking figure as the longest priced runner in the first race. Thanks for trying. Compound Magic I liked the look of your "improved formula" and tried that but encountered a problem when the formula came across a new race as the ranking figure didn't reset to 1. I've not used ISBLANK & ISTEXT functions before so hadn't a clue as to how to resolve the problem. Finally I tried your initial suggestion and that didn't work at first either! Similar problem to the one I've just described. However as the formula was a little closer to my level of expertise(!) I had a little tinker and found a solution by adding the text shown in red to the formula you'd provided: =IF(A2=A3;IF(E2=E3;H1;H1+1);1). Works a treat now - my thanks to everyone for their help. Thanks |