STA 200: Statistics: Excel Project 2

STA 200: Statistics: Excel Project 2

· To calculate the correlation factor (r), go to Excel, Formulas, Statistics, and select the CORREL function.

· To calculate the Regression Equation and Coefficient of Determination:

a. Select the x,y data

b. Insert a scatter chart from that data

c. Click the chart, go to Chart Design

d. Go to Quick Layout

e. Select Layout 9

f. Then you get the Regression Equation, and r square.

Using MS Excel, prepare a Simple Linear Regression for the following data: In the data that follows, X=median diameter (in millimeters) of granules of sand, and Y=gradient of beach slope in degrees on natural ocean beaches.

X 0.17 0.19 0.22 0.235 0.235 0.30 0.35 0.42 0.85

Y 0.63 0.70 0.82 0.88 1.15 1.50 4.40 7.30 11.30

Then using MS Excel:

1) Find the sample mean and standard deviation for x and y. 2) Calculate the Coefficient of Correlation (r). 3) Make a scatter plot. 4) If there is correlation, then find the equation of the least-square line or regression line, and then graph the line on the scatter plot. 5) Find the coefficient of determination r**2 (r square).