In: Statistics and Probability
A study tried to relate the number of calories in beer to the percentage of alcohol and number of carbohydrates. Use the accompanying data table to perform a natural logarithmic transformation of the dependent variable (calories). Using the transformed dependent variable and the percentage of alcohol and the number of carbohydrates as the independent variables, perform a multiple regression analysis. Then answer parts (a) through (f). a. State the regression equation, Fstat, and critical value
how do you find each variable in the regression equation? please show steps in excel.
Calories (Y) | Alcohol % (X1) | Carbohydrates (X2) |
94 | 4.1 | 3.2 |
153 | 4.8 | 12.5 |
147 | 5.4 | 8.8 |
144 | 4.9 | 10.6 |
168 | 5.8 | 12.5 |
174 | 6.2 | 11.1 |
147 | 4.5 | 13.4 |
147 | 4.8 | 13.9 |
149 | 5.5 | 9.8 |
109 | 4.1 | 7.2 |
143 | 5.8 | 6.6 |
152 | 4.7 | 13.8 |
104 | 4.1 | 5.7 |
144 | 4.5 | 12.9 |
151 | 4.6 | 14.1 |
112 | 4.2 | 6.7 |
143 | 4.7 | 13.1 |
109 | 4.1 | 6.9 |
98 | 4.4 | 3.4 |
113 | 3.7 | 8.2 |
146 | 4.7 | 11.8 |
179 | 5.7 | 14.2 |
159 | 4.7 | 17.9 |
159 | 4.9 | 19.9 |
165 | 5.2 | 16.9 |
162 | 4.8 | 19.6 |
Excel Goto Data > Data analysis > Regression > select y and then in x select both x1 and x2
> ok
================================
SUMMARY OUTPUT | ||||||||
Regression Statistics | ||||||||
Multiple R | 0.9761 | |||||||
R Square | 0.9527 | |||||||
Adjusted R Square | 0.9486 | |||||||
Standard Error | 5.4806 | |||||||
Observations | 26 | |||||||
ANOVA | ||||||||
df | SS | MS | F | Significance F | ||||
Regression | 2 | 13923.17641 | 6961.58821 | 231.76339 | 5.72984E-16 | |||
Residual | 23 | 690.8620476 | 30.0374803 | |||||
Total | 25 | 14614.03846 | ||||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
Intercept | -4.8713 | 8.5026 | -0.5729 | 0.5723 | -22.4602 | 12.7177 | -22.4602 | 12.7177 |
Alcohol % (X1) | 22.5361 | 1.8404 | 12.2450 | 0.0000 | 18.7289 | 26.3434 | 18.7289 | 26.3434 |
Carbohydrates (X2) | 3.3352 | 0.2525 | 13.2063 | 0.0000 | 2.8128 | 3.8577 | 2.8128 | 3.8577 |
================================
Regression equation
==================================
Fstat = 231.76339
==================================
Fcrit = F.INV.RT(0.05,2,23) = 3.4221
==================================
x1= It is positive and statistically significant , because p-value is less than 0.05
same for x2