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