Regression Analysis - Confidence Interval of the Slope

 

It is common in science and engineering to make a graph of experimental data for the purpose of discovering the slope. Often we need to report the slope with a confidence interval. For example, we may need to report the value of the slope is 1.23 0.34. The diagram shows an example of this calculation. Click here to get sample workbook.

 

 

The confidence interval is computed using where t is the critical value of the t statistic and Sb is the standard error in the slope.

 

The t value is computed as =TINV(a,df). If we were to use a table to look up t at the 95% confidence level, we would look the t value corresponding to a =(1-95%) = 0.025. However, since in the Excel function TINV, a is the probability associated with the two-tailed Student's t-distribution, we use a value of

1 - 95% = 0.05. The degree of freedom is the number of observations less 2.

 

The Sb value is readily obtained from the LINEST function. If we do not need all the LINEST output we may use =INDEX(LINEST(y,x,1,1), 2,1)

 

Note: the plus-minus symbol () is produced with the keystrokes ALT+0177.

Return to Excel Tips & Tricks