I've been working on a project for a while for a friend...he's into horse racing. I know zilch about horses...
There's a file that you can purchase, and my friend does, that for each day at a given track has historical data for the past ten races for each horse that is running that day. They're big .csv files, with ~1400 fields per line. The code I've written parses the file and then, using some formulas he has created, produces a handicapping report to guide his bets.
So far everything has worked well, but his latest request has me a bit stumped. Here's the basic situation.
Part of the report has a column for each horse called EP (early pace) and MP (middle pace). A sample might look like
[TABLE="width: 56"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]EP[/TD]
[TD]MP[/TD]
[/TR]
[TR]
[TD]77[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]89[/TD]
[TD]93[/TD]
[/TR]
[TR]
[TD]85[/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD]67[/TD]
[TD]64
[/TD]
[/TR]
</tbody>[/TABLE]
He wanted a add a column that is a calculated speed metric. It works like this:
Average the EP and MP.
For the horse with the maximum average, enter a positive number that is how far ahead of the next best
For the remaining horses, enter a negative number that is how far behind the best
So for the above data, the new column looks like this
[TABLE="width: 84"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD="colspan: 3"]-22.5[/TD]
[/TR]
[TR]
[TD="colspan: 3"]-13.0[/TD]
[/TR]
[TR]
[TD="colspan: 3"]-11.5[/TD]
[/TR]
[TR]
[TD="colspan: 3"]-17.0[/TD]
[/TR]
[TR]
[TD="colspan: 3"]+11.5[/TD]
[/TR]
[TR]
[TD="colspan: 3"]-37.0
[/TD]
[/TR]
</tbody>[/TABLE]
This shows that the fifth horse had the best average, and was 11.5 better than the next best horse (which was number 3). Number 3 had the second best and is 11.5 behind the best. Number 2 was next best, and was 13 behind the best, and so on.
I have this all implemented in code and it works fine. The issue is that sometime horses scratch and don't race. For every other field in the report this is no big deal, you simply delete the row that corresponds to that horse and everything is fine. At worst, conditional formatting highlights the new max value/top 3/etc in the column. But since this column relies on the values of the other horses to calculate, deleting a horse can completely change the values. If, for instance, the fifth horse (best) scratched, then the number 3 horse is now the best, and his entry should change to +1.5. Horse 2 would be second best and so his entry would become -1.5, etc.
The only way I can see to make that happen is if, rather than calculating the values and populating the cells, I populate the cells with a formula using calculated ranges...but I'm nowhere near proficient enough is rank, index, etc to make that happen. Particularly since what we're ranking is a value that never even appears on the sheet (the average of EP and MP). I guess I could drop those values to the sheet way off to the right where they're outside the print area and no one ever sees them. Then have an if that does one thing for rank 1 and something else for all the others....but I'm still not sure how to use rank, index, etc to pull that off.
Any help?
Thanks in advance.
There's a file that you can purchase, and my friend does, that for each day at a given track has historical data for the past ten races for each horse that is running that day. They're big .csv files, with ~1400 fields per line. The code I've written parses the file and then, using some formulas he has created, produces a handicapping report to guide his bets.
So far everything has worked well, but his latest request has me a bit stumped. Here's the basic situation.
Part of the report has a column for each horse called EP (early pace) and MP (middle pace). A sample might look like
[TABLE="width: 56"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]EP[/TD]
[TD]MP[/TD]
[/TR]
[TR]
[TD]77[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]89[/TD]
[TD]93[/TD]
[/TR]
[TR]
[TD]85[/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD]67[/TD]
[TD]64
[/TD]
[/TR]
</tbody>[/TABLE]
He wanted a add a column that is a calculated speed metric. It works like this:
Average the EP and MP.
For the horse with the maximum average, enter a positive number that is how far ahead of the next best
For the remaining horses, enter a negative number that is how far behind the best
So for the above data, the new column looks like this
[TABLE="width: 84"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD="colspan: 3"]-22.5[/TD]
[/TR]
[TR]
[TD="colspan: 3"]-13.0[/TD]
[/TR]
[TR]
[TD="colspan: 3"]-11.5[/TD]
[/TR]
[TR]
[TD="colspan: 3"]-17.0[/TD]
[/TR]
[TR]
[TD="colspan: 3"]+11.5[/TD]
[/TR]
[TR]
[TD="colspan: 3"]-37.0
[/TD]
[/TR]
</tbody>[/TABLE]
This shows that the fifth horse had the best average, and was 11.5 better than the next best horse (which was number 3). Number 3 had the second best and is 11.5 behind the best. Number 2 was next best, and was 13 behind the best, and so on.
I have this all implemented in code and it works fine. The issue is that sometime horses scratch and don't race. For every other field in the report this is no big deal, you simply delete the row that corresponds to that horse and everything is fine. At worst, conditional formatting highlights the new max value/top 3/etc in the column. But since this column relies on the values of the other horses to calculate, deleting a horse can completely change the values. If, for instance, the fifth horse (best) scratched, then the number 3 horse is now the best, and his entry should change to +1.5. Horse 2 would be second best and so his entry would become -1.5, etc.
The only way I can see to make that happen is if, rather than calculating the values and populating the cells, I populate the cells with a formula using calculated ranges...but I'm nowhere near proficient enough is rank, index, etc to make that happen. Particularly since what we're ranking is a value that never even appears on the sheet (the average of EP and MP). I guess I could drop those values to the sheet way off to the right where they're outside the print area and no one ever sees them. Then have an if that does one thing for rank 1 and something else for all the others....but I'm still not sure how to use rank, index, etc to pull that off.
Any help?
Thanks in advance.
