SIMPLE LINEAR REGRESSION

To carry out linear regression in Excel, select Tools > Data analysis... > Regression. Enter the percent cell range in the Y range box, and the depth cell range in the X range box.

You will need to have removed the row with the missing depth before doing the regression or you will get an error message about non-numeric data. Ensure that the labels box is checked if necessary and click on OK to obtain the output on the right.

The standard error is calculated as the square root of the Error Mean Square (MS). R squared indicates how much of the total variation in the dependent variable (y) is explained by the regression. The closer R squared is to 100, the better the fit of the line to the data. R squared is the square of the coefficient of correlation (when the variables are random). Adjusted R-square is the R-sq. adjusted for degrees of freedom.

Next comes the Analysis of Variance table, with the p-value from the hypothesis that the variables are independent (i.e. the variable percent does not change as depth changes).

The final table gives detailed information about the regression equation. The first column contains the regression coefficients which describe the best fitting straight line:

percent = 22.6 + 2.23 depth

The other columns give further information about these coefficients, including their standard deviation. These values are useful for obtaining confidence intervals of the intercept and slope. The t-ratio and the p-value of a coefficient test the hypothesis that the coefficient is 0. The last columns specify confidence intervals for the parameter estimates at both the 5% level and at the level you specified in the initial regression dialog box.

SIMPLE LINEAR REGRESSION

Basic statistics in Excel 23.2.99 Page: 24 of 25