Question

Instructions for Excel Assignment

a) Using the data in the file (i.e., the values of Y and values of X), create a scatter plot with the variable Y plotted vertically and the variable X plotted horizontally. You are to add a trendline, the equation of the line and R2 to this scatter plot. You do not need to add a title to this plot.

b) Run the ‘Regression’ analysis tool (found under DATA/Data Analysis). The output using this tool should result in 3 tables. The values in the table are to be formatted to 5 decimal places except for number of observations and df’s. Make certain that when your columns are wide enough so that no descriptions in your output are cut-off.

х Y 12 51 18 38 13 41.4 27 34 19 45.1 32 23.7 30 28 36 10.7 8 54 39 15.7

Answer 1

The scatter plot 60 50 40 > 30 20 Linear (Y) y = -1.284x+64.206 R?=0.9154 10 0 0 10 20 30 40 50 х The regression analysis output SUMMARY OUTPUT Regression Statistics Multiple R 0.95679 R Square 0.91544 Adjusted R Square 0.90487 Standard Error 4.48450 Observations 10 ANOVA df F 86.60516 Significance F 0.00001 1 MS 1741.69772 20.11078 Regression Residual Total SS 1741.69772 160.88628 1902.58400 8 9 Intercept X Variable 1 Coefficients 64.20610 -1.28402 Standard Error 3.52633 0.13797 t Stat 18.20761 -9.30619 P-value 0.00000 0.00001 Lower 95% 56.07436 -1.60219 Upper 95% 72.33784 -0.96585