In: Statistics and Probability
Q2: Using MS Excel Analyze a regression of Air Quality on three covariates CO2, NOx, and Sox
Data Set is as following
Air Quality |
CO2 |
NOx |
SOx |
197 |
61 |
22 |
17 |
191 |
33 |
16 |
27 |
224 |
65 |
18 |
18 |
183 |
55 |
18 |
32 |
236 |
50 |
26 |
23 |
200 |
60 |
24 |
26 |
226 |
59 |
16 |
25 |
164 |
54 |
22 |
24 |
100 |
83 |
10 |
17 |
285 |
50 |
21 |
29 |
207 |
73 |
22 |
21 |
336 |
61 |
26 |
35 |
299 |
42 |
36 |
21 |
192 |
44 |
25 |
4 |
264 |
54 |
20 |
29 |
244 |
24 |
17 |
27 |
227 |
53 |
22 |
42 |
216 |
41 |
18 |
30 |
263 |
57 |
29 |
24 |
265 |
87 |
23 |
25 |
How to use CO2, NOx, and SOx to predict the Air Quality? Please provide the formula.
What is the correlations between Air Quality and each of the three factors? What do they mean?
What is the Coefficient of Determination (R Square) of the regression result? What does it mean?
Solution:
Go to data >data analysis >Regression
select Y as Air quality
Rest all as X variables
You will get'
SUMMARY OUTPUT | ||||||
Regression Statistics | ||||||
Multiple R | 0.739169 | |||||
R Square | 0.546371 | |||||
Adjusted R Square | 0.461316 | |||||
Standard Error | 38.24893 | |||||
Observations | 20 | |||||
ANOVA | ||||||
df | SS | MS | F | Significance F | ||
Regression | 3 | 28193.26 | 9397.755 | 6.423705 | 0.004621 | |
Residual | 16 | 23407.69 | 1462.98 | |||
Total | 19 | 51600.95 | ||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | |
Intercept | 40.78836 | 62.1144 | 0.656665 | 0.52073 | -90.8883 | 172.465 |
CO2 | -0.17858 | 0.591485 | -0.30191 | 0.766606 | -1.43247 | 1.075315 |
NOx | 5.964901 | 1.605223 | 3.715932 | 0.001878 | 2.561979 | 9.367822 |
SOx | 2.681185 | 1.136653 | 2.358843 | 0.031382 | 0.271589 | 5.090781 |
Regression equation is
Air quality=40.78836-0.17858*CO2+5.964901*NOx+2.681185*SOx
Air Quality | CO2 | NOx | SOx | |
Air Quality | 1 | |||
CO2 | -0.1844 | 1 | ||
NOx | 0.614144 | -0.12749 | 1 | |
SOx | 0.368603 | -0.12929 | -0.06312 | 1 |
there exists a negaive relationship between air quality and C02
there exists a positive relationship between air quality and NOx
there exists a positive relationship between air quality and SOx
Solutionc:
What is the Coefficient of Determination (R Square) of the regression result? What does it mean?
R sq=0.546371
=0.546371*100
=54.64% variation in Air quality is explained by model.