How to calculate the uncertainty of slope in Excel

Calculating the uncertainty of the slope is important in various fields and applications, particularly in scientific research and data analysis. The uncertainty of the slope provides an indication of the reliability or confidence we can have in the estimated slope value. It quantifies the range of values within which the true slope is likely to lie.

There are many different methods to calculate the uncertainty of slope in Excel, but Excel also provides a function for calculating slope and uncertainty of slope, that is called LINEST function.

We’ll discuss two variations of this function in this tutorial.

Case 1 – Using LINEST function in four cells

In MS Excel, we can calculate the uncertainty of slope using the LINEST function with minor variations. If we select four cells in a 2×2 manner, We’ll get four different results. The top-left one is the “Slope”, the bottom-left one is the Uncertainty of the slope, the top-right one is the “Y-intercept”, and the bottom-right one is the “Uncertainty of the Y-intercept”. Consider the following dataset that contains temperatures seven days a week:

Step 1 – Select four cells 

• Choose any cell range of four cells.
• Press and drag to select the cells in a 2×2 manner.
• Label all 4 cells as shown below to get appropriate tags for the results. 

Step 2 – Use the LINEST function

• After selecting the cells, write the appropriate formula for calculating the uncertainty of the slope using the LINEST function.
• The syntax of the LINEST function is =LINEST(known y’s, known x’s,[const],[stats]).
• In this case, the used formula is =LINEST(B2:B8, A2:A8,1,1).
• Write this formula in the formula bar.
• It is important to implement the function by pressing Ctrl+Shift+Enter to apply this function as an array formula for the selected cell range.
• The value in the bottom left cell is the uncertainty of the slope.

Case 2 – Using LINEST function in two cells

We can also calculate the uncertainty of a slope by using the LINEST function in two cells. The difference, in this case, is that it gives the result of “Slope”, and “Uncertainty of slope”. Consider the same dataset that is used for the above example:

Step 1 – Select two cells vertically

• Choose any two cells.
• Press and drag over the two cells to select them.

Step 2 – Use the LINEST function

• After selecting the cells, write the appropriate formula for calculating the uncertainty of the slope using the LINEST function.
• The syntax of the LINEST function is =LINEST(known y’s, known x’s,[const],[stats]).
• In this case, the used formula is =LINEST(B2:B8, A2:A8,1,1).
• Write this formula in the formula bar.
• Press Ctrl+Shift+Enter to apply the formula to the selected cell range.
• The value in the lower cell is the uncertainty of the slope.

Break-down of the used formula

We know that the syntax of the LINEST function is =LINEST(known y’s, known x’s,[const],[stats]). The syntax of the LINEST function indicates that it has four parameters, which are known x’s, known y’s,[const], and [stats]. In “known x’s”, we have to write the cell range that contains values of the X-axis, whereas in “known y’s”, we have to write the cell range that has values of the Y-axis. In [const], we have to write 1 if we want to calculate b normally, or 0 if we want to set b to 0. In [stats], we have to write 1 if we want to return additional regression statistics, or 0 if we don’t want to.