EXCEL Regression HELP!

[martymart2u]

Need help with excel to solve this !! Big Brains only! How do I compute the % in excel




[TABLE="width: 596"]
<tbody>[TR]
[TH]Team[/TH]
[TH]Offensive Run Efficiency[/TH]
[TH]Offensive Pass Efficiency[/TH]
[TH]Defensive Run Efficiency[/TH]
[TH]Defensive Pass Efficiency[/TH]
[TH]Win %[/TH]
[TH]Points Spread[/TH]
[/TR]
[TR]
[TD]Seattle[/TD]
[TD]1.13[/TD]
[TD]1.18[/TD]
[TD]1.04[/TD]
[TD]0.88[/TD]
[TD]65%[/TD]
[TD]-4[/TD]
[/TR]
[TR]
[TD]Atlanta[/TD]
[TD]0.86[/TD]
[TD]1.05[/TD]
[TD]1.12[/TD]
[TD]1.04[/TD]
[TD]35%[/TD]
[TD]+4[/TD]
[/TR]
</tbody>[/TABLE]


Seattle’s 1.27 on the ground and 1.22 through the air, gave them potentially an advantage over
Atlanta’s 0.89 on the ground and 0.93 through the air.


Quantifying this advantage is made relatively easy by the NFL's reluctance to allow tied games. Very few matches remain scoreless in overtime, so we have a sport where there are almost always only two possible results for the home team. So we can use logistic regression, which demands just two possible outcomes and a representative sample of historical match ups to predict how often such a game as Seattle's at Atlanta would result in a home win.


For those interested in the technical details of the regression, the respective rushing and passing coefficients for the home side are currently 1.04 and 1.91, -0.99 and -1.88 for the visitors and the constant is 0.22. If we apply those numbers to last week's NFC Divisional game between Seattle and Atlanta we get -0.62, we'll call this number X. The final step to convert X to a win probability for the home team is to insert it into this equation;


Home Win Probability = e^(X)/(1+(e^(X))


For our example the win probability for the home team, Atlanta, came to 0.35. Seattle was therefore a 0.65 chance, giving them a likely average margin of victory of about 4 points and value against the spread, where Atlanta were favoured by about a field goal. On the day the Falcon's drove 40 yards in the final 25 seconds to beat the Seahawks by 2.

 

[martymart2u]

• How did this person come up with 65%?

• Need help with excel to solve this !! Big Brains only! How do I compute the % in excel

[TABLE="width: 596"]
<tbody>[TR]
[TH]Team[/TH]
[TH]Offensive Run Efficiency[/TH]
[TH]Offensive Pass Efficiency[/TH]
[TH]Defensive Run Efficiency[/TH]
[TH]Defensive Pass Efficiency[/TH]
[TH]Win %[/TH]
[TH]Points Spread[/TH]
[/TR]
[TR]
[TD]Seattle[/TD]
[TD]1.13[/TD]
[TD]1.18[/TD]
[TD]1.04[/TD]
[TD]0.88[/TD]
[TD]65%[/TD]
[TD]-4[/TD]
[/TR]
[TR]
[TD]Atlanta[/TD]
[TD]0.86[/TD]
[TD]1.05[/TD]
[TD]1.12[/TD]
[TD]1.04[/TD]
[TD]35%[/TD]
[TD]+4[/TD]
[/TR]
</tbody>[/TABLE]


Seattle’s 1.27 on the ground and 1.22 through the air, gave them potentially an advantage over
Atlanta’s 0.89 on the ground and 0.93 through the air.


Quantifying this advantage is made relatively easy by the NFL's reluctance to allow tied games. Very few matches remain scoreless in overtime, so we have a sport where there are almost always only two possible results for the home team. So we can use logistic regression, which demands just two possible outcomes and a representative sample of historical match ups to predict how often such a game as Seattle's at Atlanta would result in a home win.


For those interested in the technical details of the regression, the respective rushing and passing coefficients for the home side are currently 1.04 and 1.91, -0.99 and -1.88 for the visitors and the constant is 0.22. If we apply those numbers to last week's NFC Divisional game between Seattle and Atlanta we get -0.62, we'll call this number X. The final step to convert X to a win probability for the home team is to insert it into this equation;


Home Win Probability = e^(X)/(1+(e^(X))


For our example the win probability for the home team, Atlanta, came to 0.35. Seattle was therefore a 0.65 chance, giving them a likely average margin of victory of about 4 points and value against the spread, where Atlanta were favoured by about a field goal. On the day the Falcon's drove 40 yards in the final 25 seconds to beat the Seahawks by 2.

 

[martymart2u]

Here is an example

Example link


https://docs.google.com/<wbr>spreadsheets/d/1PNVcg_<wbr>kQ4KDgl4C2ko2lnTh0WgKMrkPcJBBv<wbr>HAXSuHU/edit?usp=sharing

 

[shg]

I expect the argument to the logistic function, X, is some regression on eight variables, maybe like

X = a*(myORE - theirDRE) + b*(myOPE - theirDPE) + c*(myDRE - theirORE) + d*(myDPE - theirOPE)

The calculation for X isn't shown, and you don't have the data to do the regression yourself, so ...

 

[shg]

Looking back at this, maybe all the information is there -- but the post provides only 4 coefficients to apply to eight statistics (plus a constant), and I don't see how those 8 statistics get combined and reduced to 4.