A Basic Working Curve Plot
with Regression Line
and LLS Calculation of Errors using STEYX()

©David L. Zellmer, Ph.D.
Department of Chemistry
California State University, Fresno

Starting with the basic techniques of graphing and use of the LINEST() function then plotting multiple series and estimating errors, we now will use more Excel statistical functions such as STEYX() to calculate the errors of our Linear Least Squares fit.

In cell C15 we find the Excel function STEYX(y-values,x-values), which computes the standard error about regression. This is basically the standard deviation of the differences about the regression line. The X and Y arrays are the same as those used in the LINEST() function in the previous tutorial. Note that the value of STEYX is about 0.03 absorbance units, which is what we estimated for our error in the second tutorial.
We can use this value of STEYX to draw error limits on our plot. Before doing this, however, we must review what we learned about Confidence Limits. One standard deviation includes about 68% of our data, while 1.96 standard deviations contain 95% of our data. (We will save the effect N on the error and of degrees of freedom on Student's t for the next tutorial.) So for a quick and dirty estimate of 95% confidence limits, we will multiply STEYX by 1.96 as shown in cell C16.
The error in concentration is easily computed from the error in absorbance by taking the derivative of y=mx+b. dy=mdx, so the error in x (concentration) is just the error in y (absorbance), divided by m (the slope). The result of dividing by the slope to get the concentration error is found in cell C18 and is =C16*C15/A15. Note that we have included our 1.96 multiplier for this error, making it about twice as big as the one we estimated in a previous tutorial.
The job of actually producing a plot with all the error limits showing would be quite complicated to show in a step-by-step fashion, so I will give you some hints and let you play with the graph yourself.
- The X, Y, and regression values are computed and plotted as shown in previous tutorials.
- The upper error limit is computed by adding the computed error from STEYX*1.96 to the regression value. So D6=C6+$C$16*$C$15.
- The lower error limit is computed by subtracting the computed error from STEYX*1.96 from the regression value. So E6=C6-$C$16*$C$15. Then fill these formulas down.
- The Chart Wizard gets very unhappy if you try to have it plot more than two data series, so set up the chart with three columns (two data series) as before.
- Once the chart has been created, additional series can be added to it. To add the upper error limit in column D, (1) drag over the data in column A, then (2) hold down the Command key (Mac), or the Control key (PC), and drag over the corresponding data in column D. (3) Copy these data with Command-C (Mac). (4) Put the graph in Chart Mode by clicking on it enough times to change it and select it. (5) Choose Paste Special from the Edit Menu. (6) A dialog box will come up asking you if the first column of data is to be X-values for an XY plot. Choose that option and click OK. You have now added this series to the existing plot. (7) Repeat this process for column E.
- Now click on each series on the chart and change the markers and line types to get the graph as shown. You'll get it to work after three or four tries (or more).
In the next tutorial you will learn how to use the official error equation for linear least squares analysis. The only problem is that the official formula may grossly overestimate the error when the regression line contains only three or four points, as ours typically may. The estimates shown here are probably more realistic.

Return to LLS Basic Master Page

For questions or comments, contact Dr. David Zellmer at david_zellmer@csufresno.edu.

Last Updated: 8 February 1997

A Basic Working Curve Plotwith Regression Lineand LLS Calculation of Errors using STEYX()

A Basic Working Curve Plot
with Regression Line
and LLS Calculation of Errors using STEYX()