Calculating the odds that the Warriors will win the title

[JenniferMurphy]

I have been doing some calculations on the Vegas odds for the NBA Championship series. I'd like to calculate the odds for every possible combination of wins and losses for the rest of the playoffs. I'd like to set up a table something like this:

[TABLE="class: grid, width: 789"]
<tbody>[TR]
[TD="align: center"]C/R[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Game 3[/TD]
[TD="align: center"]Game 4[/TD]
[TD="align: center"]Game 5[/TD]
[TD="align: center"]Game 6[/TD]
[TD="align: center"]Game 7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Results[/TD]
[TD="align: center"]Away[/TD]
[TD="align: center"]Away[/TD]
[TD="align: center"]Home[/TD]
[TD="align: center"]Away[/TD]
[TD="align: center"]Home[/TD]
[TD="align: center"]Prob[/TD]
[TD="align: center"]Formula[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]4-0[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]40.6%[/TD]
[TD]I9: =PWAway*PWAway[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]4-1[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]19.4%[/TD]
[TD]I10: =PWAway*PLAway*PWHome[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]4-1[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]19.4%[/TD]
[TD]I11: =PLAway*PWAway*PWHome[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]4-2[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2.4%[/TD]
[TD]I12: =PWAway*PLAway*PLHome*PWAway[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]4-2[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3.1%[/TD]
[TD]I13: =PLAway*PWAway*PLHome*PWHome[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]4-2[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"][/TD]
[TD="align: center"]9.3%[/TD]
[TD]I14: =PLAway*PLAway*PWHome*PWHome[/TD]
[/TR]
</tbody>[/TABLE]

I have named variables defined for the probability of wins and losses at home and on the road. Their current values are:

• 84.0% = PWHome
• 16.0% = PLHome
• 63.7% = PWAway
• 36.3% = PLAway

I'd like to calculate the values in Col I based on the values in each row and in Row 8 (home or away). I currently have formulas that I entered manually. This is highly error-prone. I;d like a generic formulas I can put in I9:I14 that will generate the same calculations.

I assume it is some type or array expression, but I can't get my head around it.

Any suggestions?


Go Warriors!!!


mmm

 

[sheetspread]

Either unpivot (normalise) the first table and match/multiply or Transpose the win-loss prob table and sumproduct

 

[sheetspread]

Something like:

Excel 2010
	C	D	E	F	G	H	I	J
6
7		Game 3	Game 4	Game 5	Game 6	Game 7
8	Results	Away	Away	Home	Away	Home	Prob	Formula
9	4-0	W	W				40.60%	40.58%
10	4-1	W	L	W			19.40%	19.42%
11	4-1	L	W	W			19.40%	19.42%
12	4-2	W	L	L	W		2.40%	2.36%
13	4-2	L	W	L	W		3.10%	2.36%
14	4-2	L	L	W	W		9.30%	7.05%
15
16	Home	Home	Away	Away
17	W	L	W	L
18	0.84	0.16	0.637	0.363
Sheet4

Cell Formulas
Range	Formula
J9	{=PRODUCT(1/(IFERROR(1/SUMIFS($C$18:$F$18,$C$16:$F$16,$D$8:$H$8,$C$17:$F$17,D9:H9),1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

is the non-normal method but calculating single columns (or adding a table of percentages for each game) works better.

 

[JenniferMurphy]

Thanks, spreadsheet. Obviously, I didn't get around to this until after the series was over...because it was over so quickly!!!


Sorry, that was poor sportsmanship.

I'll keep this around to study before next season when they go for 4 in 5 years!

 

[sheetspread]

It's fine, thanks for the update!