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
Back to Lay-Multiple Calculations
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
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.
I made a greenup spreadsheet ages ago which you can tinker with to utilise the VBA if it's any use.
Last edited by spreadbetting on Thu Mar 22, 2018 3:59 pm, edited 1 time in total.
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
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
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
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.
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.
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
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
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
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
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
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
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
=ROUND((F3*A3)/E3 ,2) in column B
and
=ROUND((B3-F3)*(100-D3)/100,2) in column C
Last edited by spreadbetting on Thu Mar 22, 2018 9:45 pm, edited 1 time in total.
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
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
You do not have the required permissions to view the files attached to this post.
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
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
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