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