A Basic Working Curve Plot with Linear Least Squares Calculation of Concentration and Regression Line

©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 we can add some features to make the actual line appear. From the numbers generated we can even estimate the errors in the fit. (The values used in this tutorial have more error than in the previous one to more easily show differences from regression.)

From the slope and the intercept previously calculated we can compute the Regression Values. These are the values of "Y" computed from mx+b. The formula found in cell C6 is =$A$15*A6+$B$15. The dollar signs ($) make the references absolute. As the formula is filled down, these references will not change. The A6, however, will change to point to successive x-values, thus generating all the regression values. We started the series with a zero to get the line to go all the way to the origin of the graph.
After entering the formula into A6, use the mouse to drag the formula down to the last x-value, then choose Fill Down from the Edit Menu.
The Chart Wizard will graph more than one series of Y-values. Use the mouse to select all three columns of numbers, from A6 to C10. Select the Chart Wizard and draw the graph as before. You will end up with two sets of markers showing the two data series.
This next part is easy to show in person, but difficult to describe.
- Click twice anywhere on the graph to put Excel into Chart mode. Depending on the version of Excel you are using, a different type of border around the chart will tell you the mode has changed.
- Find one of the markers that belongs to the Regression series. Click on it to select the series.
- Now double click on the selected series. A dialog box will come up that lets you change the lines and markers. The diagram below comes from Excel 5.0.
- Set the Line to Automatic, or select Custom dashed lines as was done here, and set the Markers to None.
- The graph should now appear as shown in the figure.
From the amount of variation we see between the regression values and the experimental values, we can estimate the errors in absorbance. Inspection of the values shows a difference of about 0.03 absorbance units. Given our unknown at 0.544 absorbance units, we would estimate the percentage error in our unknown at about (0.03/0.544)*100% = 5.5%.
In the next tutorial, we will make a more "official" calculation of the error.

Return to LLS Basic Master Page

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

Last Updated: 8 February 1997