Quote:
Originally Posted by JJMartin
Can you provide an example?
|
Not easily without posting a file a lot of which has some stuff I don't want seen, doing a youtube video would be best but I don't have time for that. Anyway I can talk through it a bit.
In row one starting in column B I have headers for various factors going across the top. In A2 I have Ranked 1, A3 I have Ranked 2, and so on Ranked 3 then Ranked FH and Ranked RH, then Empty / Unknown in A7.
Then in A8 I have the row headers starting for the dataset itself. So A8 is RaceID and B8 is RankFactor1, C8 is RankFactor2 and so on.
Then in A9 begins the dataset so it's some specific horse in a race, and the ranks are shown from B9 through I9. J8 through Q8 are all headers Score1, Score2, the next row down are all point values, these cells have formula in them along the lines of IF(B9=1,B2
What that means is essentially if that horse in that race had a ranking of 1 for that factor then populate this cell with whatever point value is in B2.
R9 has total points for all of the factors defined so it's a sum of the horse's scores on the factors to the left of it. If you had more factors of course this would be positioned more to the right and the entire dataset would begin lower etc it's just how I set it up for solver.
What solver then does is you're going to set that up to change those point values in the range from B2 to in this case I7 that range is defined in the solver where you specify 'by changing variable cells'. Set them all at 0 to start with and I like to run it set to evolutionary.
You obviously need something to optimize on. The important thing here would be to have a column for finish position (in this sheet I had it in column V) and then I have some column letter S in this case named GroupRank that is populated with formula like =RANK.AVG( i.e. of that first horse's total points(R9) in the group of horses in that race, which was from R9 to R13
What you're looking for here is when a horse has a group rank of 1 and the finish position is also 1 then that returns a $ amount and of course counts as a win.
So the total wins vs bets are easy enough to calculate win% I have a label WPCT in V1 and cell V2 the actual Win%, Win% in this case was defined as my 'set objective' cell to Maximize in Solver. It doesn't have to be Win%, you could optimize on ROI or anything odds related, though I would use caution there, as there's a ton of fitting to noise in payoffs, I would want a massive dataset if I were fitting to ROI. Anyway, you also have to set some constraints on point values so Solver stays within reasonable bounds and then just let it run until it gets happy.
The devil in the details all rests in the GroupRank column formulas, as long as you get past that you can start to play around.
