Question

Excel LINEST function. Enter the following data in a spreadsheet and use LINEST to find slope, intercept, and standard errors. Use Excel to draw a graph of the data and add a trendline. Draw error bars of$\mathbf{\pm }{\mathbf{s}}_{\mathbf{y}}$on the points.

$\begin{array}{cccccc}x:& 3.0& 10.0& 20.0& 30.0& 40.0\\ y:& -0.074& -1.411& -2.584& -3.750& -5.407\end{array}$

Short answer

The slope of the given data is$-0.13789$

The intercept of the given data is$0.195343$

The standard error of the given data is$0.197625$

The graph of the given data by the use of the LINEST function is given in step 2.

Step-by-step solution

Determine standard deviation of y : sy and the standard uncertainty of the slope and the intercept

The standard deviation ofyis mathematically represented as

${\mathbf{\sigma }}_{\mathbf{y}}\mathbf{\approx }{\mathbf{s}}_{\mathbf{y}}\mathbf{=}\sqrt{\frac{\mathbf{\sum }_{\mathbf{}}\mathbf{\left(}{\mathbf{d}}_{\mathbf{i}}^{\mathbf{2}}\mathbf{\right)}}{\mathbf{n}\mathbf{-}\mathbf{2}}}$.

The standard uncertainty of a slope is mathematically represented as

${\mathbf{u}}_{\mathbf{m}}^{\mathbf{2}}\mathbf{=}\frac{{\mathbf{s}}_{\mathbf{y}}^{\mathbf{2}}\mathbf{n}}{\mathbf{D}}$.

The standard uncertainty of an intercept is mathematically represented as

${\mathbf{u}}_{\mathbf{b}}^{\mathbf{2}}\mathbf{=}\frac{{\mathbf{s}}_{\mathbf{y}}^{\mathbf{2}}\mathbf{\sum }_{\mathbf{}}\left(x_i^2\right)}{\mathbf{D}}$.

Determine the slope, intercept, and standard errors

The given data is listed below.

Use the Excel LINEST function to calculate the slope, the intercept, and the standard errors.

Use Excel to draw the graph, the error bars of $\pm {\mathrm{s}}_{\mathrm{y}}$on the points.

Excel LINEST function:

The Excel LINEST function used (Figure 1) to calculate the slope, the intercept and the standard errors; and to draw a graph is shown in Figure 2.

From the spreadsheet, the slope is $-0.13789$, the intercept is $0.195343$and the standard error found is $0.197625$