How do I do binomdist in Excel?

Ganchrow · 08-31-07, 02:39 AM

Originally posted by Degenerate

Im not very good with Excel,
I cant figure out how to do binomdist, hopefully someone can help

do I need an add in for Excel?
If anyone can point me to a website or book that would help me with this it would be much appreciated!!

I have set lines for NFL season wins, and now I want to use that to work out how often a team wins an exact number of games

eg: I have CHI <10 -133 as fair value, and want to work out from that how often will they end the season with 0wins - 16 wins

If you think that the fair value of Chicago Under 10 is -133, this implies a conditional probability of 133/(133+100) = 57.082% for winning fewer than 10 games and a conditional probability of 1-57.082% = 42.918% for winning more than 10 games.

Assuming all games have equal win probabilities (a highly specious assumption to be sure) this implies a single-game win probability of 60.4858% (because =(binomdist(16,16,60.4858%,0) + binomdist(15,16,60.4858%,0) + binomdist(14,16,60.4858%,0) + binomdist(13,16,60.4858%,0) + binomdist(12,16,60.4858%,0) + binomdist(11,16,60.4858%,0) ) / (1 - binomdist(10,16,60.4858%,0) ) ≈ 42.918%).

Therefore, the probability of going 0-16 would be given by =binomdist(0,16,60.4858%,0) ≈ 0.000035322%.

Degenerate · 08-31-07, 04:28 AM

Thanks for the reply Ganch, I thought you would know how to do it

The results i get using 60.4858% win probability

Wins Probability
0 3.53225E-07
1 8.6511E-06
2 9.93191E-05
3 0.000709479
4 0.003529583
5 0.012966863
6 0.036389541
7 0.079575389
8 0.137035008
9 0.186457236
10 0.199791735
11 0.166815468
12 0.106396003
13 0.050112052
14 0.016437495
15 0.003354862
16 0.000320963

I'm still not sure how you calculated the 60.4858% win prob., would you be able to explain that to me in simple terms?

Ganchrow · 08-31-07, 05:33 AM

Originally posted by Degenerate

The results i get using 60.4858% win probability

Wins Probability
0 3.53225E-07

-snip-

You got it.

Originally posted by Degenerate

I'm still not sure how you calculated the 60.4858% win prob., would you be able to explain that to me in simple terms?

You need to solve for the single game win probability that yields a probability of winning X or more games equal to that implied by the fair total and over/under lines.

This Excel spreadsheet demonstrates the process. Enter in the "fair" season total and the "fair" odds on the over and under, then click the button labeled "Get Single Game Win Probability". (Note: this spreadsheet uses Microsoft Excel Solver. If you don't have Excel 2003 or don't have it installed in the default location you may need to manually add a reference to Solver in VBA. See the section of this page entitled "Checking the reference to Solver.xla" for instructions on how to do this.)

Degenerate · 09-03-07, 07:54 AM

Thx Ganch,
I read the link, have nt managed to get it working yet, but ill figure it out eventually

In the mean time could you give me the single game win probabilities for the following (I left it abit late to take some NFL futures and now im scrambling to get it done)

IND <10.5 -125

NE <11.5 -102

SD <10.5 +130

Thx again Ganch

Ganchrow · 09-03-07, 08:15 AM

Originally posted by Degenerate

In the mean time could you give me the single game win probabilities for the following:

IND <10.5 -125
NE <11.5 -102
SD <10.5 +130

Assuming 20¢ spreads:

IND 10.5u -125 → 64.3370%
NE 11.5u -102 → 71.8932%
SD 10.5u +130 → 67.6270%