Have Excel pick a random number between 0 and 9 (you can do this with the functi
ID: 3321410 • Letter: H
Question
Have Excel pick a random number between 0 and 9 (you can do this with the function =Randbetween (0, 9) ), using 2000 pairs of random numbers. Then ask Excel to run the linear regression between these two columns and generate a p-value for testing the significance of the linear relationship between X and Y. Use a significance level (alpha) of 10%. Have Excel calculate the slope, intercept, correlation, p-value.
My results found:
Trial #
Slope
Intercept
Correlation
P-Value of Slope
Significant?
1
-0.01
4.50
0.01
0.56
No
***QUESTION TO BE ANSWERED:
Calculate the 95% prediction interval of y when x=5 using the 2000 pairs.
Trial #
Slope
Intercept
Correlation
P-Value of Slope
Significant?
1
-0.01
4.50
0.01
0.56
No
Explanation / Answer
As all required data not available the calculation is redine using 2000 random data pairs in excel.
Result of the same is given below :
95% prediction Interval
a + bx0 +/- t0.025 * se * Sqrt(1/n +(x0-xbar)2/Sxx)
Parameters are
a
b
t0.025
Se
n
xo
xbar
Sxx
4.3607
0.005
1.96
2.889928
2000
5
4.513
16097.66
Hence Confidence Interval is
4.3857
+/-
0.128509
Calculation for Sxx
xbar
4.513
Sum xi sqr
56832
sum xi
9026
Sqr(sumxi)
81468676
Sqr(sumxi)/n
40734.34
Sxx = Sum xi sqr - Sqr(sumxi)/n
16097.66
SUMMARY OUTPUT Regression Statistics Multiple R 0.005065 R Square 2.57E-05 Adjusted R Square -0.00047 Standard Error 2.889928 Observations 2000 C ANOVA df SS MS F Significance F Regression 1 0.428115 0.428115 0.051261 0.820907 Residual 1998 16686.66 8.351682 Total 1999 16687.09 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 90.0% Upper 90.0% Intercept 4.360726 0.121419 35.91466 2.3E-218 4.122605 4.598848 4.160917 4.560536 X Variable 1 0.005157 0.022777 0.226409 0.820907 -0.03951 0.049827 -0.03233 0.04264Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.