Betfair 'What if' formula

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
Bobsmilk
Posts: 9
Joined: Sat Feb 02, 2019 1:44 am

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?
spreadbetting
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
Bobsmilk
Posts: 9
Joined: Sat Feb 02, 2019 1:44 am

spreadbetting wrote:
Thu May 16, 2019 6:32 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

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.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”