I'm trying to put together a spreadsheet that immitates Betfairs main market screen which includes the 'what if' formula to show the expected outcome should all bets be matched.
Its not the most eloquent of solutions, but I have got this far:
https://docs.google.com/spreadsheets/d/ ... 1082548480
I'm no great shakes with excel and am missing two formulae:
1: What if / expected outcome
2: Win only commission in B2L/L2B column
I realise that both BF & BA calculate these automatically though I need this for an automation project I am working on
Does anyone know how to apply these?
Betfair 'What if' formula
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
To calculate what ifs you'd simply calculate the two outcomes, one for win and one for lose and then add in the respective aggregate lay stakes less aggregate back stakes of the other runners. Remember all calculations assume every bet is matched.
So for your top runner if we say C11 is the lay result winning your calculation is simply
=((J9-1)*K9)-(J11-1*K11)+(K12+K15+K18+K21+K24+K27+K30+K33+K36+K39)-(K14+K17+K20+K23+K26+K29+K32+K35+K38+K41)
And we'll put the back result in E11 which would be assuming the back bet won
=((J11-1)*K11)-((J9-1)*K9)+(K12+K15+K18+K21+K24+K27+K30+K33+K36+K39)-(K14+K17+K20+K23+K26+K29+K32+K35+K38+K41)
Haven't tested it cos I can't edit your file
To include commission you'd simply do an IF statement and IF the result was positive you multiply by (100 - commission rate)/100
So for your top runner if we say C11 is the lay result winning your calculation is simply
=((J9-1)*K9)-(J11-1*K11)+(K12+K15+K18+K21+K24+K27+K30+K33+K36+K39)-(K14+K17+K20+K23+K26+K29+K32+K35+K38+K41)
And we'll put the back result in E11 which would be assuming the back bet won
=((J11-1)*K11)-((J9-1)*K9)+(K12+K15+K18+K21+K24+K27+K30+K33+K36+K39)-(K14+K17+K20+K23+K26+K29+K32+K35+K38+K41)
Haven't tested it cos I can't edit your file
To include commission you'd simply do an IF statement and IF the result was positive you multiply by (100 - commission rate)/100
spreadbetting wrote: ↑Thu May 16, 2019 6:32 pmTo calculate what ifs you'd simply calculate the two outcomes, one for win and one for lose and then add in the respective aggregate lay stakes less aggregate back stakes of the other runners. Remember all calculations assume every bet is matched.
So for your top runner if we say C11 is the lay result winning your calculation is simply
=((J9-1)*K9)-(J11-1*K11)+(K12+K15+K18+K21+K24+K27+K30+K33+K36+K39)-(K14+K17+K20+K23+K26+K29+K32+K35+K38+K41)
And we'll put the back result in E11 which would be assuming the back bet won
=((J11-1)*K11)-((J9-1)*K9)+(K12+K15+K18+K21+K24+K27+K30+K33+K36+K39)-(K14+K17+K20+K23+K26+K29+K32+K35+K38+K41)
Haven't tested it cos I can't edit your file
To include commission you'd simply do an IF statement and IF the result was positive you multiply by (100 - commission rate)/100
Thank you spreadbetting - I wouldn't have been able to finish that without help
Here is a link for anyone that would like to download a copy:
https://docs.google.com/spreadsheets/d/ ... sp=sharing
You do not have the required permissions to view the files attached to this post.