[cincy]

I have an Excel Spreadsheet that caclulates the probability of a team winning each of its remaining games. For example, say I have a team with win probabilities for their remaining 5 games as:
.48
.66
.70
.47
.52

What Excel equation do I use to determine the probability of winning exactly 2 games or exactly 3 games?

[tto827]

Dunno how you got these numbers from math not knowing how to do the next step.
You are going to have to write the equation yourself, but its pretty basic. Check out khanacademy videos, they should help you out.

[Melloweitsj]

Pr(Winning exactly 2 games) = 26.55%
Pr(Winning exactly 3 games) = 34.91%

[allin1]

tto thanks for mentioning khanacademy vieos. I didn't know about those until now. Found some very useful stuff there.

[tto827]

tto thanks for mentioning khanacademy vieos. I didn't know about those until now. Found some very useful stuff there.

No worries, I use it all the time to catch up on my stats.

[cincy]

Thanks but I wanted to know the excel equation that I would use to figure out the probability. For example, if my above 5 numbers are in cells A1 to A5 and I want to know the probability of exactly 2 wins to be put in cell A7 what equation would I put in cell A7?

[Maverick22]

Well what is the formula to calculate the numbers on paper?

[Inspirited]

You have to multiply the chances of winning 2 of the games with the chances of losing the other 3 games. Do this for every possible combination and add them up. There are 10 combinations for exactly two out of five. You should really try to do this yourself now. Someone up there gave the answers so see if you can get them.

P(W1)*P(W2)*(1-P(W3))*(1-P(W4))*(1-P(W5))

[cincy]

I realize I could create a table with all the 32 possibilities and figure this one problem out but I want to be able to figure out the win probabilities also when the season starts and there are 16 games so that is just too many possibilities to put in a table. I know Excel has an analysis toolpack add-on and Excel is very powerful and likely has a simple equation that I can enter in cell A7 to figure this out. For example, Excel has a function called BINOMDIST that can sometimes be used to figure out the exact probability but I think the BINOMDIST function only works if the win probablity is the same for each game. Excel also has a function called HYPGEOMDIST but I am not sure if this applies here or how to enter the correct information to use the HYPGEOMDIST. If anyone has used this Excel function or knows an equation that I could put in cell A7 then please let me know. Thanks.

[Blax0r]

Dunno how you got these numbers from math not knowing how to do the next step.
You are going to have to write the equation yourself, but its pretty basic. Check out khanacademy videos, they should help you out.

I don't like to troll, but the first sentence tto827 writes is a really REALLY big point... This sort of calculation should be trivial compared to getting the game probabilities.

Anyway, if your game p's are all different, there's no way around trying every combination (as far as I know). I highly recommend writing a program, such as Excel's VBA (alt+f11), to do this; you'll probably need to generate the list of all combos, and then plug in your probabilities for each element of that list.

Feel free to post/PM some code; I (and probably many others) would be happy to review.

[Ganchrow]

I have an Excel Spreadsheet that caclulates the probability of a team winning each of its remaining games. For example, say I have a team with win probabilities for their remaining 5 gamess:
...
What Excel equation do I use to determine the probability of winning exactly 2 games or exactly 3 games?

Check out the EnumCombin VBA function in the latest posted version of my Simple VBA Sports Betting Functions Template for Excel.

I also posted a C++ implementation that links from VBA and is often faster (and occasionally much slower) but only runs on Win32: http://www.sportsbookreview.com/forum/handicappe...xcel-demo.html.

I seem to recall that somewhere in one of those threads I once demonstrated the function.

Anywy, I think it's what you want.

[cincy]

Thanks but I have no idea how to write a program and don't know anything about VBA or C++ but it sounds like something that would be useful to me, I just don't know where to start. I did download the Copy of Function Demo Excel spreadsheet and see that P2L calculates the exact win probabilities for 0,1,2,3,4 wins based on 4 different win percentages. How do I modify this to calculate the exact win probabilities for 0 to 16 wins based on 16 different win percentages? My NFL team spreadsheet calculates the win probabilities for each of the teams remaining games and it shows a 1 for 100% for games already played that a team won and shows a 0 for 0% for games already played that a team lost. So I want to know the exact probabilites of each season win total from 0 to 16 at any point during the season. For example, with 5 games left my spreadsheet might show the following in cells BJ22 to BJ37 for a teams win probabilities:

0.00
1.00
1.00
1.00
0.00
0.00
0.00
0.00
1.00
1.00
1.00
0.48
0.66
0.70
0.47
0.52

I want the spreadsheet to show the probability of 0 wins in cell bm22, 1 win in cell bm23,... 16 wins in cell bm38

Thanks for the help.

[Peregrine Stoop]

I created a big ass spreadsheet with 1s and 0s and multiplying to do every possible combination of wins for 20 game probabilities... it sucks, but it works