View New Posts
123
1. Originally Posted by dday0707
I tried the enumcombin formula but it's not working. Can u pls elaborate and give another simple example. I tried for example to list the following 5 teams Man Utd, Liverpool, Chelsea, Arsenal & Aston Villa.
Could you please provide an example of what you've tried as well as be more specific about what you're looking to accomplish?

Are you sure you've properly entered the formula as an array function?

Originally Posted by dday0707
Also is there a way let say if I want to cover 2 options i.e either each team to win or draw. so for 5 teams with 2 options will make me 32 possibilities. Can we have your formula to do it.
Again, could you please be more specific about what you're looking to accomplish? For what sort of output are you looking?

SBR Founder Join Date: 8/28/2005

2. Ok. Find attached an excel sheet withan example.

THX

3. Originally Posted by dday0707
Ok. Find attached an excel sheet withan example.

THX
Makes use of a new (still undocumented) function I just added: {=ENUMCOMBIN3(Range, Size, "WinLabel", "DrawLabel")}.

WinLabel and DrawLabel default to values of "WIN" and "DRAW" respectively.

Output dimensions are {Range size} columns x {2^Size} rows.

Sample output (transposed) found in A19:H21.

SBR Founder Join Date: 8/28/2005

4. Originally Posted by Ganchrow
Makes use of a new (still undocumented) function I just added: {=ENUMCOMBIN3(Range, Size, "WinLabel", "DrawLabel")}.

WinLabel and DrawLabel default to values of "WIN" and "DRAW" respectively.

Output dimensions are {Range size} columns x {2^Size} rows.

Sample output (transposed) found in A19:H21.
HI,

That's COOL. It's working like charm.
THANK YOU VERY MUCH
U R A GENIUS

5. Whats the quick formula to determine which middle to choose?

Example 1

Spurs -3.5 -116
Cavs +5.5 -113

Pays +1379

Example 2

Spurs -3.5 -130
Cavs +4.5 +105

Pays +1786

Whats a quick formula to calculate the payout ratios, assume im buying enough of both sides so that the position is neutral vig wise.

The formula im currently using is a bit bulky.

6. Originally Posted by Pancho sanza
Whats the quick formula to determine which middle to choose?

Example 1

Spurs -3.5 -116
Cavs +5.5 -113

Pays +1379

Example 2

Spurs -3.5 -130
Cavs +4.5 +105

Pays +1786

Whats a quick formula to calculate the payout ratios, assume im buying enough of both sides so that the position is neutral vig wise.

The formula im currently using is a bit bulky.
I'm sorry Pancho, but I don't understand your question.

SBR Founder Join Date: 8/28/2005

7. Lets say San Antonio is playing Cleveland.

I get an alert 15 minutes prior to tip saying Lebron is out for the Cavs.

Lets say I lay all the spurs I can find knowing I will later be able to take Cleveland for a +ev middle.

The market has now settled, I add up all the spurs i got down for, lets assume i got down 5800/5000 at -3.5, so im sitting with an open position of San Antonio -3.5 -116 to win 5k.

I now look at the market and only have a few minutes to close my middle.

I can either take Cleve +6.5 -110, Cleve +5.5 +105

What is a quick formula to determine how much cavs i need to take back at either price assuming I want to pay equal vig should the middle not hit.

I have a homemade solver type formula that solves for this but it sometimes doesn't round properly.

I think the us2hold formula will solve for this somehow.

8. Let dopen = decimal odds on open bet
Let dnew = decimal odds on new bet
Let M = money wagered on open bet
Let X = necessary money to wager on new bet in order to equalize losses

M*(dopen-1) - X = X*(dnew-1) - M
X = M * dopen / dnew

So in the examples you gave:
M = \$5,800
dopen

new ML = -110
dnew
X ≈ \$5,800 * 1.86207 / 1.90909 ≈ \$5,657.15

new ML = +105
dnew =
X ≈ \$5,800 * 1.86207 / 2.05 ≈ \$5,268.30

That said, equalizing losses should not generally be the goal of the advantage middle player. Ostensibly, your first bet was +EV, while the second was at-market.

As such, you'd likely want to risk less on the second wager than indicated above, leaving yourself some additional exposure to the first wager. The decision of exactly how much to wager on the second could best be answered by appealing to a staking strategy such as Kelly.

SBR Founder Join Date: 8/28/2005

9. Excellent, thank you.

Now lets say i wanted to solve for the payout ratios (assuming the middle hits) from the vig on each leg.

Option 1 (taking +6.5 -110) pays +1544

Option 2 (taking +5.5 +105) +3925

How would I solve for those ratios given I know the vig on both ends?

Lets assume I entered the following on my excel sheet.

A1 -116
A2 -110

A1 -116
A2 +105

10. If by payout ratio you mean the synthetic fractional odds on the middle then:

To win = 2*dopen - 1 - dopen / dnew
At risk = dopen - 1 - dopen / dnew

So the win to risk ratio would be:
dopen * dnew / ( dopen + dnew - dopen * dnew) - 1

Or in your Excel example:
=US2DEC(A1)*US2DEC(A2)/(US2DEC(A1)+US2DEC(A2)-US2DEC(A1)*US2DEC(A2))-1

Generally speaking, computations are much easier when you maintain your odds in decimal format.

SBR Founder Join Date: 8/28/2005

11. Excellent thanks.

12. Originally Posted by Ganchrow
That said, equalizing losses should not generally be the goal of the advantage middle player. Ostensibly, your first bet was +EV, while the second was at-market.

As such, you'd likely want to risk less on the second wager than indicated above, leaving yourself some additional exposure to the first wager. The decision of exactly how much to wager on the second could best be answered by appealing to a staking strategy such as Kelly.
... and using the spreadsheet posted in this thread: http://www.sportsbookreview.com/forum/handicappe...readsheet.html

13. Originally Posted by Data
... and using the spreadsheet posted in this thread: http://www.sportsbookreview.com/forum/handicappe...readsheet.html
One might think I'd be more familiar with my own spreadsheets ...

SBR Founder Join Date: 8/28/2005

• MB2US(US Matchbook Exchange Odds, Commission {default=1%})
• MB2Dec(Decimal Matchbook Exchange Odds, Commission {default=1%})

These calculate sportsbook equivalent US or Decimal odds given US or Decimal Matchbook exchange odds and commission. This references a commission structure where the player pays a set percentage of the lesser of risk or win irrespective of bet outcome. So =US2MB(-110, 1%) would refer to the sportsbook equivalent odds of betting at -110 given 1% Matchbook commission (~-112.12).

SBR Founder Join Date: 8/28/2005

15. Added for Version: 1.0.1.13:

• SBRVer(): Displays current template version number.
• {US2Fair(range of US Odds)} or {Dec2Fair(range of Decimal Odds)}: (array function) Returns an array of fair value zero-vig odds based on an Excel range of US or decimal odds. So if cells A1 and A2 are -200 and +176, respectively, and if B1 and B2 were set to the array formula {=US2Fair(A1:A2)}, B1 and B2 would display the values of -184 and +184, respectively.

Full function list and download in first post of thread

SBR Founder Join Date: 8/28/2005

16. One more for Ganchrow.

Lets say I know the edge on a middle and the hit frequency, kelly says risk X on the middle, lets assume X = \$1000 in this case.

I then have to leg the position, risking \$1000 total.

These the lines on the game im middling:

Spurs -4.5 -114
Pacers +7.5 -121

What is the formula to determine how much of each I grab so that my net risk on the middle is \$1000, assume im taking enough of each to equalize the loss should the middle not hit.

17. Originally Posted by Pancho sanza
These the lines on the game im middling:

Spurs -4.5 -114
Pacers +7.5 -121

What is the formula to determine how much of each I grab so that my net risk on the middle is \$1000, assume im taking enough of each to equalize the loss should the middle not hit.
Putting aside Kelly, if you were simply looking to equalize losses should your middle not hit, you'd wager as follows:

Spurs -4.5 -114
=10000*us2dec(-121)/(us2dec(-121)+us2dec(-114))
= \$4,931.49
Pacers +7.5 -121
=10000*us2dec(-114)/(us2dec(-121)+us2dec(-114))
= \$5,068.51

SBR Founder Join Date: 8/28/2005

18. How come you used 10,000 Ganch?

Doesn't quite equalize the losses:

Spurs Risk/Win @-114 = 5621.9/4931.49
Pacers Risk/Win @ -121 = 6132.89/5068.51

Spurs win by 6 net profit = 10,000 (+4931.49 +5068.51)
Spurs win by 20 net loss = -1201.4 (+4931.49 -6132.89)
Pacers win by 20 net loss = -553.39 (+5068.51 -5621.9)

OK, what did i do wrong here.

19. Those are risk quantities not win quantities.

Spurs Risk/Win @-114 = \$4,931.49/\$4,325.87
Pacers Risk/Win @ -121 = \$5,068.51/\$4,188.85

Spurs win by 6 net profit = +\$8,514.72 (\$4,325.87 + \$4,188.85)
Spurs win by 20 net loss = -\$742.64 (\$4,325.87 - \$5,068.51)
Pacers win by 20 net loss = -\$742.64 (\$4,188.85 - \$4,931.49)

But I misunderstood your question and thought you were looking to risk \$10,000 total across both bets.

If you're looking to win \$10,000 should the middle hit then you'd wager as follows:

Spurs -4.5 -114:
=10000*us2dec(-121)/(2*us2dec(-121)*us2dec(-114) - us2dec(-121) - us2dec(-114))
=\$5,791.72 to win \$5,080.46

Pacers +7.5 -121:
=10000*us2dec(-114)/(2*us2dec(-121)*us2dec(-114) - us2dec(-121) - us2dec(-114))
=\$5,952.64 to win \$4,919.54

This way you'd stand to win \$5,080.46 + \$4,919.54 = \$10,000 should your middle hit and stand to lose -\$5,791.72 + \$4,919.54 = \$5,080.46 - \$5,952.64 = -\$872.18 should your middle miss.

SBR Founder Join Date: 8/28/2005

20. Got it, thanks again.

21. Hi there,

would it be please possible to prepare excel formula for "evaluating" asian handicap bets? For me it is quite complicated, but it would be very useful for those of us, who bet soccer.

Thank you!

Tom

22. Not sure exactly for what you're looking but do check out http://www.sportsbookreview.com/forum/handicappe...tml#post332398 and Asian Handicap Middles.

SBR Founder Join Date: 8/28/2005

23. Hello Ganch - I have applied the VBA SBF to Excel 2007, works great. Thank you for sharing. Question - I am trying to point a cell with ProbUS2Edge to cells with existing values. I.E. =ProbUS2Edge(E38,I38). E38=.5754 I38=(-120) the result is .0549. If appling this to a cell that has been inserted the cell displays "0" otherwise works fine Can this be altered?

24. Originally Posted by 1
Hello Ganch - I have applied the VBA SBF to Excel 2007, works great. Thank you for sharing. Question - I am trying to point a cell with ProbUS2Edge to cells with existing values. I.E. =ProbUS2Edge(E38,I38). E38=.5754 I38=(-120) the result is .0549. If appling this to a cell that has been inserted the cell displays "0" otherwise works fine Can this be altered?
I'm not actually following.

Could you be a bit more specific?

Have you tried hitting the F9 (Calc Now) key?

SBR Founder Join Date: 8/28/2005

25. Yes. F9 = nothing
The correct result (.0549) is shown in any cell the formula is entered except in a cell that has been posterior inserted to the worksheet. Formula bar shows =ProbUS2Edge(E38,I38) cell result displays "0"

26. Ganch - Another false alarm. Sorry! The cell width was small and the result was under the adjacent cell except for the zero before the decimal. I guess I will have some penance?

27. Awesome! The only question I had was on the formula used to calculate matchbook odds as I've been having difficulty figuring that out. Thanks a lot!

28. Originally Posted by roasthawg
Awesome! The only question I had was on the formula used to calculate matchbook odds as I've been having difficulty figuring that out. Thanks a lot!
VBA function for converting gross US Matchbook odds to net US Odds (i.e., odds after commission):
Code:
```Public Function MB2US(ByVal dUSOdds As Double, Optional ByVal dCommission = 0.01) As Double
Application.Volatile
If dUSOdds < 0 Then
MB2US = Dec2US( _
1 + (1 - dCommission) / _
(-dUSOdds / 100 + dCommission) _
)
Else
MB2US = Dec2US( _
1 + (dUSOdds / 100 - dCommission) / _
(1 + dCommission) _
)
End If
End Function```

SBR Founder Join Date: 8/28/2005

29. i dont need most of them but some are very handy to me
thanks

30. First off, I want to say that these excel commands are excellent and save me a bunch of time. However, I'm running into a problem with the SBKelly() function. Any time I use it in my spreadsheet, it instantly makes Solver run exponentially slower. What confuses me is that my solver set up is on a different sheet. The SBKelly() function isn't even using the numbers generated by Solver. But if I use the SBKelly() function over just 60 rows, it triples the time it takes me to open and run Solver. Any ideas?

*As it turns out, Excel is slowing down any time I use some other functions from your template. Just adding one column of simple us2dec() calcs is making Solver crawl.*

31. Don't expect any help.

32. Originally Posted by RickySteve
Don't expect any help.
Care to elaborate on that? Is there a reason I shouldn't expect any help?

33. He doesn't post here any more.

34. Originally Posted by RickySteve
He doesn't post here any more.
Did Ganchrow = pags11????

No, in all seriousness, what happened to him? I thought he went to CR to help out SBR?

35. Originally Posted by Ganchrow
VBA function for converting gross US Matchbook odds to net US Odds (i.e., odds after commission):
Code:
```Public Function MB2US(ByVal dUSOdds As Double, Optional ByVal dCommission = 0.01) As Double
Application.Volatile
If dUSOdds < 0 Then
MB2US = Dec2US( _
1 + (1 - dCommission) / _
(-dUSOdds / 100 + dCommission) _
)
Else
MB2US = Dec2US( _
1 + (dUSOdds / 100 - dCommission) / _
(1 + dCommission) _
)
End If
End Function```
right say!
Points Awarded:
 Matt Rain gave shantystar 5 SBR Point(s) for this post.

SBR Founder Join Date: 11/13/2005

First 1234 Last
Top