- 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).