Hi
I have a rather major issue which has just arisen and I am hoping there is a solution.
A few days after the end of each month, the data results file is downloaded, formatted to suit the sheet it is to go the data is added, then the profit and loss calculations take place. That's the easy part and up until a couple of weeks ago, it was a pretty normal procedure. Unfortunately there has been a change at the data supplier whereby the number of runners listed (column F) no longer matches the number of placings listed (column AC). This is not the case in all races, but the only ones it really affects is anything around 8, 9 or even possibly 10 runners.
Pretty much all over the world, a dividend is paid to 3rd place horses in races of 8 runners or more. If there are 7 runners or less, then only 2 placings are paid and less than 4, only 1st place is paid.
So the issue here is that for whatever reason, the data supplier is now listing the original number of runners when the race is announced; let's say 8. During the race day, 2 horses are withdrawn making only 6 horses start, therefore now only paying 1st and 2nd. The P&L formula I have always used cannot differentiate this and puts a profit amount where it shouldn't be. This is very new and the data for April 2021 was fine, but for May, it has now changed.
Here is the P&L formula I use
This has always worked fine, as I mentioned, but now I need to have a way of checking that if, say, F3>=8 but the corresponding results in AC only show 7, or 6 placings or less, then it is listed as a loss.
There is a further issue which is that not all placings are listed as where they finished. There some codes which begin with 5 and are 3 digits, ie 501, 509 and so on. These mean things like Unseated The Rider, Pulled Up and so on. The numbers are not so important, as all it means is the horse started, but did not finish.
As an example of a possible 8 horse race, AC may look like this
1
2
3
4
5
6
509
512
This still shows there were 8 runners which started.
Now to help, I always have the data sorted as follows, A - Date, C - Time, H - Track, AC - Finish Position & Q - Horse. This at least means that the finish positions are consecutive.
I am able to write some code to do the matching part, but not to include checking number of runners vs Finish Position. On any given day, if A, C & Q match, then it is the same horse. I just need a way of telling it that if A, C & Q match and F3>=8, AND the corresponding cells in AC show 8 entries, then it uses the profit formula, if not, it is a loss.
As I said, the matching part is easy and I have this code which does it for me, thanks to one of the members here helping me sort out a time issue
I can change the sheet references as required, but what can be added to check the requirement I have? It just needs to check that if F3>=8 AND the corresponding cells in AC have 8 entries, then use the (Z3-1)*98 profit formula, otherwise it is a loss.
Here is what an incorrect one looks like
You can see there are 8 runners listed in F, but only 6 entries in AC
I hope that helps and there is a fix
I have a rather major issue which has just arisen and I am hoping there is a solution.
A few days after the end of each month, the data results file is downloaded, formatted to suit the sheet it is to go the data is added, then the profit and loss calculations take place. That's the easy part and up until a couple of weeks ago, it was a pretty normal procedure. Unfortunately there has been a change at the data supplier whereby the number of runners listed (column F) no longer matches the number of placings listed (column AC). This is not the case in all races, but the only ones it really affects is anything around 8, 9 or even possibly 10 runners.
Pretty much all over the world, a dividend is paid to 3rd place horses in races of 8 runners or more. If there are 7 runners or less, then only 2 placings are paid and less than 4, only 1st place is paid.
So the issue here is that for whatever reason, the data supplier is now listing the original number of runners when the race is announced; let's say 8. During the race day, 2 horses are withdrawn making only 6 horses start, therefore now only paying 1st and 2nd. The P&L formula I have always used cannot differentiate this and puts a profit amount where it shouldn't be. This is very new and the data for April 2021 was fine, but for May, it has now changed.
Here is the P&L formula I use
=IF(OR(AND(F3>=4,F3<=7,AC3<=2), AND(F3>=8,AC3<=3)),(Z3-1)*98,-100)This has always worked fine, as I mentioned, but now I need to have a way of checking that if, say, F3>=8 but the corresponding results in AC only show 7, or 6 placings or less, then it is listed as a loss.
There is a further issue which is that not all placings are listed as where they finished. There some codes which begin with 5 and are 3 digits, ie 501, 509 and so on. These mean things like Unseated The Rider, Pulled Up and so on. The numbers are not so important, as all it means is the horse started, but did not finish.
As an example of a possible 8 horse race, AC may look like this
1
2
3
4
5
6
509
512
This still shows there were 8 runners which started.
Now to help, I always have the data sorted as follows, A - Date, C - Time, H - Track, AC - Finish Position & Q - Horse. This at least means that the finish positions are consecutive.
I am able to write some code to do the matching part, but not to include checking number of runners vs Finish Position. On any given day, if A, C & Q match, then it is the same horse. I just need a way of telling it that if A, C & Q match and F3>=8, AND the corresponding cells in AC show 8 entries, then it uses the profit formula, if not, it is a loss.
As I said, the matching part is easy and I have this code which does it for me, thanks to one of the members here helping me sort out a time issue
=IFERROR(INDEX('[2021 Latest Results January 2021.xlsb]VDW-Place-Adapted-2021-01-01-20'!$AE$3:$AE$103352,MATCH(1,INDEX((A3='[2021 Latest Results January 2021.xlsb]VDW-Place-Adapted-2021-01-01-20'!$A$3:$A$103352)*(ROUND(C3,5)=ROUND('[2021 Latest Results January 2021.xlsb]VDW-Place-Adapted-2021-01-01-20'!$C$3:$C$103352,5))*(Q3='[2021 Latest Results January 2021.xlsb]VDW-Place-Adapted-2021-01-01-20'!$Q$3:$Q$103352),0,1),0)),"No")
I can change the sheet references as required, but what can be added to check the requirement I have? It just needs to check that if F3>=8 AND the corresponding cells in AC have 8 entries, then use the (Z3-1)*98 profit formula, otherwise it is a loss.
Here is what an incorrect one looks like
| 2021 Latest Results May 2021.xlsb | |||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | F | Q | AC | AD | AE | ||||||||||||||||||||||||||
| 33525 | 4/23/21 | Friday | 15:15 | 8 | Kakamora | 1 | 144.06 | 62.72 | |||||||||||||||||||||||||
| 33526 | 4/23/21 | Friday | 15:15 | 8 | Good Boy Bobby | 2 | -100 | 147 | |||||||||||||||||||||||||
| 33527 | 4/23/21 | Friday | 15:15 | 8 | Fugitives Drift | 3 | -100 | -100 | |||||||||||||||||||||||||
| 33528 | 4/23/21 | Friday | 15:15 | 8 | Tayzar | 4 | -100 | -100 | |||||||||||||||||||||||||
| 33529 | 4/23/21 | Friday | 15:15 | 8 | Getareason | 501 | -100 | -100 | |||||||||||||||||||||||||
| 33530 | 4/23/21 | Friday | 15:15 | 8 | Big Bad Bear | 512 | -100 | -100 | |||||||||||||||||||||||||
2021 Latest Results May 2021 | |||||||||||||||||||||||||||||||||
You can see there are 8 runners listed in F, but only 6 entries in AC
I hope that helps and there is a fix
