Back to Lay-Multiple Calculations

[boardrep]

Hi Chaps/Chapesses
Im hoping one of the Excel people could supply me with the formula for my spreadsheet to calculate easily a series of back to lay bets in terms of profit after commission(coloumn C) as the one Im using/ thought Id worked out doesnt seem to match Chromawebs or similar figure
Ive attached a screenie if someone could point me int the right direction please
thanks in advance

Board

[spreadbetting]

Not too sure what you're after, the actual greenup calculation is pretty simple so you simply re-arrange it if you want a target profit etc Not clear what you mean by a series of lays, are you sending more than one bet to close out the original bet at various odds? If so you'd just calculate those as percentages of your profit and calculate them separately.

I made a greenup spreadsheet ages ago which you can tinker with to utilise the VBA if it's any use.

[boardrep]

Hi there and thank you for the reply
I can see your spreadsheet and how it works for say a single odds number then to look at variations of profit vs odds

I was looking specifically at a formula that can calculate the difference between my two coloumns of odds per line for a set backing liability
in the case of my basic spreadsheet the top line is £10 -1.07 back to 1.05 lay what is the formula I need to insert to acertain profit with say 5% commission

Thank you again

[spreadbetting]

Still not sure which spreadsheet is yours and what it's trying to do or which cell the round formula is in.

When you say "set backing liability" do you mean stake or actual liability? And when you say certain profit what do you mean? If your opening odds and closing odds are fixed you can only calculate either the potential profit to a set stake or a stakes needed to acheive a set profit.

[boardrep]

Hi Spreadbetting
really appreciate your help

Ive made this much more complicated than it is

I require the formula that I could insert into any line of a spreadsheet to calculate what the profit would be if I backed at one price and layed at another

so 1.07 to 1.05 backing with £10 would require me to lay at 1.05 with an amount of 10.19 to achieve a green both sides profit of 0.18 as says Chromaweb with its flash pop up calculator

if I wanted to sum a whole coloumn of different odds what would be the formula I would need to insert to find the profit per line rather than punch each one into chromaweb
sorry Im not particularly intelligent or articulate and I certainly dont understand VBA

[spreadbetting]

Think I'm a bit clearer

So you have the following details fixed Opening Odds,Closing Odds and Opening stakes?

Just remember our green up calculations are simply a balanced equation

Opening_stakes x Opening_Odds = Closing_stakes x Closing_Odds

Any profit would simply be the difference between the Closing_stakes less the Opening_stakes =

So to calculate Closing_stakes we simply rearrange the formula to

Closing_stakes = (Opening_stakes x Opening_Odds)/Closing_Odds

Profit =Closing_stakes - Opening_stakes

becomes

Profit = (Opening_stakes x Opening_Odds)/Closing_Odds - Opening_stakes

to take into account commission we multiply by (100 - Commission)/100


Profit = ((Opening_stakes x Opening_Odds)/Closing_Odds - Opening_stakes) x (100 - Commission)/100

I'm not too sure what your B and C columns are supposed to show as they don't tally with the other spreadsheet shown otherwise I'd have put up some idea of the excel formulas you need to input

[boardrep]

thats perfect thanks for your patience spreadbetting

[spreadbetting]

No problem at a guess you should be entering


=ROUND((F3*A3)/E3 ,2) in column B

and

=ROUND((B3-F3)*(100-D3)/100,2) in column C

[boardrep]

Thanks again
Ive got this throughout the sheet now and sums to a number that is close to the chromaweb arb calculator but not exactly
for example

back at 6 lay and lay at 1.82 with your suggested calculation with £10 and 5% commission sums at £23.00 profit

on chromaweb £21.81

?? which is correct and maybe why the difference unless Im tying in incorrectly ? this is the original problem I encountered

[spreadbetting]

I'm guessing your commission column is formatted to display commission as a percentage so the cell is actually holding a value of 0.05 rather than 5 which my formula would use.

You can either amend your column so it holds the percentage as a number or amend the formula like so

=ROUND((B3-F3)*(1-D3),2)

Remember Betfair also round down rather than up so you may prefer to use


=ROUNDDOWN((B3-F3)*(1-D3),2)

and in B3

=ROUNDDOWN((F3*A3)/E3 ,2)

That should give you the same amounts as your chromeweb thing

[boardrep]

Ah the intracies of Excel and mathematics ,Ive changed the formatting and it now matches the thingy thank you ,I appreciate all of your help Spreadbetting albeit how simple you may think it is ,to us mere mortals its a dark art !