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