Download a ZIP file containing all the above spreadsheets (in both formats).
Note: to run these spreadsheets, you must have either Excel or OpenOffice Calc installed. I recommend either Excel 2013 or OpenOffice Version 4 ( download from OpenOffice).
Background
In analytical chemistry, the accurate quantitative measurement of the composition of samples, for example by various types of spectroscopy, usually requires that the method be calibrated using standard samples of known composition. This is most commonly, but not necessarily, done with solution samples and standards dissolved in a suitable solvent, because of the ease of preparing and diluting accurate and homogeneous mixtures of samples and standards in solution form. In the calibration curve method, a series of external standard solutions is prepared and measured. A line or curve is fit to the data and the resulting equation is used to convert readings of the unknown samples into concentration. An advantage of this method is that the random errors in preparing and reading the standard solutions are averaged over several standards. Moreover, non-linearity in the calibration curve can be detected and avoided (by diluting into the linear range) or compensated (by using non-linear curve fitting methods). There are worksheets here for several different calibration methods:
1. Download and open the desired calibration worksheet from among those listed above.
2. Enter the concentrations of the standards and their instrument readings (e.g. absorbance) into the blue table on the left. Leave the rest of the table blank. You must have at least two points on the calibration curve (three points for the quadratic method or four points for the cubic method), including the blank (zero concentration standard). If you have multiple instrument readings for one standard, it's better to enter each as a separate standard with the same concentration, rather than entering the average. The spreadsheet automatically gives more weight to standards that have more than one reading.
3. Enter the instrument readings (e.g. absorbance) of the unknowns into the yellow table on the right. You can have any number of unknowns up to 20. (If you have multiple instrument readings for one unknown, it's better to enter each as a separate unknown, rather than averaging them, so you can see how much variation in calculated concentration is produced by the variation in instrument reading).
4. The concentrations of the unknowns are automatically calculated and displayed column K. If you edit the calibration curve, by deleting, changing, or adding more calibration standards, the concentrations are automatically recalculated.
For the linear fit (CalibrationLinear.xls), if you have three or more calibration points, the estimated standard deviation of the slope and intercept will be calculated and displayed in cells G36 and G37, and the resulting standard deviation (SD) of each concentration will be displayed in rows L (absolute SD) and M (percent relative SD). These standard deviation calculations are estimates of the variability of slopes and intercepts you are likely to get if you repeated the calibration over and over multiple times under the same conditions, assuming that the deviations from the straight line are due to random variability and not systematic error caused by non-linearity. If the deviations are random, they will be slightly different from time to time, causing the slope and intercept to vary from measurement to measurement.. However, if the deviations are caused by systematic non-linearity, they will be the same from from measurement to measurement, in which case these predictions of standard deviation will not be relevant, and you would be better off using a.polynomial fit such as a quadratic or cubic. The reliability of these standard deviation estimates also depends on the number of data points in the curve fit; they improve with the square root of the number of points.
5. You can remove any point from the curve fit by deleting the corresponding X and Y values in the table. To delete a value; right-click on the cell and click "Delete Contents" or "Clear Contents". The spreadsheet automatically re-calculates and the graph re-draws; if it does not, press F9 to recalculate. (Note: the cubic calibration spreadsheet must have contiguous calibration points with no blank or empty cells in the calibration range).
6. The linear calibration spreadsheet also calculates the coefficient of determination, R 2 , which is an indicator of the "goodness of fit", in cell C37. R 2 is 1.0000 when the fit is perfect but less than that when the fit is imperfect. The closer to 1.0000 the better.
7. A "residuals plot" is displayed just below the calibration graph (except for the interpolation method). This shows the difference between the best-fit calibration curve and the actual readings of the standards. The smaller these errors, the more closely the curve fits the calibration standards. (The standard deviation of those errors is also calculated and displayed below the residuals plot; the lower this standard deviation, the better).
You can tell a lot by looking at the shape of the residual plot: if the points are scattered randomly above and below zero, it means that the curve fit is as good as it can be given the random noise in the data. But if the residual plot has a smooth shape, say, a U-shaped curve, then it means that there is a mismatch between the curve fit and the actual shape of the calibration curve; suggesting that the another curve fitting techniques might be tried (say, a quadratic or cubic fit rather than a linear one) or that the experimental conditions be modified to produce a less complex experimental calibration curve shape.
8. If you are using the spreadsheet for drift-corrected calibration , you must measure two calibration curves, one before and one after the samples are run, and record the date and time each calibration curve is measured. Enter the concentrations of the standards into column B . Enter the instruments readings for the first (pre-) calibration into column C and the date/time of that calibration into cell C5 ; enter the instruments readings for the post-calibration into column D and the date/time of that calibration into cell D5 . The format for the date/time entry is Month-Day-Year Hours:Minutes:Seconds , for example 6-2-2011 13:30:00 for June 2, 2011, 1:30 PM (13:30 on the 24-hour clock). Note: if both calibrations are run on the same day, you can leave off the date and just enter the time. In the graph, the pre-calibration curve is shown in green and the post-calibration curve is shown in red . Then, for each unknown sample measured, enter the date/time (in the same format) into column K and the instrument reading for that unknown into column L . The spreadsheet computes the drift-corrected sample concentrations in column M . Note: Version 2.1 of this spreadsheet (July, 2011) allows different sets of concentrations for the pre- and post-calibrations. Just list all he concentrations used in the "Concentration of standards" column (B) and put the corresponding instrument readings in columns C or D, or both . If you don't use a particular concentration for one of the calibrations, just leave that instrument reading blank.
Click to see larger figure
This figure shows an application of the drift-corrected quadratic calibration spreadsheet. In this demonstration, the calibrations and measurements were made over a period of several days. The pre-calibration (column C ) was performed with six standards (column B ) on 01/25/2011 at 1:00 PM. Eight unknown samples were measured over the following five days (columns L and M ), and the post-calibration (column D ) was performed after then last measurement on 01/30/2011 at 2:45 PM. The graph in the center shows the pre-calibration curve in green and the post-calibration curve in red. As you can see, the sensor (or the instrument) had drifted over that time period, the sensitivity (slope of the calibration curve) becoming smaller and curve becoming noticeably more non-linear (concave down). However, both the pre- and post-calibration curves fit the quadratic calibration equations very well, as indicated by the residuals plot and the coefficients of determination (R 2 ) listed below the graphs. The eight "unknown" samples that were measured for this test (yellow table) were actually the same sample measured repeatedly - a standard of concentration 1.00 units - but you can see that the sample gave lower instrument readings (column L ) each time it was measured (column K ), due to the drift. Finally, the drift-corrected concentrations calculated by the spreadsheet (column M on the right) are all very close to 1.00, showing that the drift correction works well, within the limits of the random noise in the instrument readings and subject to the assumption that the drift in the calibration curve parameters is linear with time between the pre- and post-calibrations.
1. Question: What is the the purpose of calibration curve?
Answer : Most analytical instruments generate an electrical output signal such as a current or a voltage. A calibration curve establishes the relationship between the signal generated by a measurement instrument and the concentration of the substance being measured. Different chemical compounds and elements give different signals. When an unknown sample is measured, the signal from the unknown is converted into concentration using the calibration curve.
2 . Question: How do you make a calibration curve?
Answer : You prepare a series of "standard solutions" of the substance that you intend to measure, measure the signal (e.g. absorbance, if you are doing absorption spectrophotometry), and plot the concentration on the x-axis and the measured signal for each standard on the y-axis. Draw a straight line as close as possible to the points on the calibration curve (or a smooth curve if a straight line won't fit), so that as many points as possible are right on or close to the curve.
4 . Question: How do I know when to use a straight-line curve fit and when to use a curved line fit like a quadratic or cubic?
Answer : Fit a straight line to the calibration data and look at a plot of the "residuals" (the differences between the y values in the original data and the y values computed by the fit equation). Deviations from linearity will be much more evident in the residuals plot than in the calibration curve plot. ( Click here for a fill-in-the-blank OpenOffice spreadsheet that does this for you . View screen shot). If the residuals are randomly scattered all along the best-fit line, then it means that the deviations are caused by random errors such as instrument noise or by random volumetric or procedural errors; in that case you can use a straight line (linear) fit. If the residuals have a smooth shape, like a "U" shape, this means that the calibration curve is curved, and you should use a non-linear curve fit, such as a quadratic or cubic fit. If the residual plot has a "S" shape, you should probably use a cubic fit. (If you are doing absorption spectrophotometry, see Comparison of Curve Fitting Methods in Absorption Spectroscopy).
6 . Question: What is the difference between a calibration curve and a line of best fit? What is the difference between a linear fit and a calibration curve.
Answer : The calibration curve is an experimentally measured relationship between concentration and signal. You don't ever really know the true calibration curve; you can only estimate it at a few points by measuring a series of standard solutions. Then draw a line or a smooth curve that goes as much as possible through the points, with some points being a little higher than the line and some points a little lower than the line. That's what we mean by that is a "best fit" to the data points. The actual calibration curve might not be perfectly linear, so a linear fit is not always the best. A quadratic or cubic fit might be better if the calibration curve shows a gradual smooth curvature.
7 . Question: Why does the slope line not go through all points on a graph?
Answer : That will only happen if you (1) are a perfect experimenter, (2) have a perfect instrument, and (3) choose the perfect curve-fit equation for your data. That's not going to happen. There are always little errors. The least-squares curve-fitting method yields a best fit, not a perfect fit, to the calibration data for a given curve shape (linear. quadratic, or cubic). Points that fall off the curve are assumed to do so because of random errors or because the actual calibration curve shape does not match the curve-fit equation.
Actually, there is one artificial way you can make the curve go through all the points, and that is to use too few calibration standards : for example, if you use only two points for a straight-line fit, then the best-fit line will go right through those two points no matter what . Similarly, if you use only three points for a quadratic fit, then the quadratic best-fit curve will go right through those three points, and if you use only four points for a cubic fit, then the cubic best-fit curve will go right through those four points. But that's not really recommended, because if one of your calibration points is really off by a huge error, the curve fit will still look perfect , and you'll have no clue that something's wrong. You really have to use more standards that that so that you'll know when something has gone wrong.
8 . Question: What happens when the absorbance reading is higher than any of the standard solutions?
Answer : If you're using a curve-fit equation, you'll still get a value of concentration calculated for any signal reading you put in, even above the highest standard. However, it's risky to do that, because you really don't know for sure what the shape of the calibration curve is above the highest standard. It could continue straight or it could curve off in some unexpected way - how would you know for sure? It's best to add another standard at the high end of the calibration curve.
9 . Question: What's the difference between using a single standard vs multiple standards and a graph?
Answer : The single standard method is the simplest and quickest method, but it is accurate only if the calibration curve is known to be linear. Using multiple standards has the advantage that any non-linearity in the calibration curve can be detected and avoided (by diluting into the linear range) or compensated (by using non-linear curve fitting methods). Also, the random errors in preparing and reading the standard solutions are averaged over several standards, which is better than "putting all your eggs in one basket" with a single standard. On the other hand, an obvious disadvantage of the multiple standard method is that it requires much more time and uses more standard material than the single standard method.
10 . Question: What's the relationship between sensitivity in analysis and the slope of standard curve ?
Answer : Sensitivity is defined as the slope of the standard (calibration) curve.
11 . Question: How do you make a calibration curve in Excel or in OpenOffice?
Answer : Put the concentration of the standards in one column and their signals (e.g. absorbances) in another column. Then make an XY scatter graph, putting concentration on the X (horizontal) axis and signal on the Y (vertical) axis. Plot the data points with symbols only, not lines between the points. To compute a least-squares curve fit, you can either put in the least-squares equations into your spreadsheet, or you can use the built-in LINEST function in both Excel and OpenOffice Calc to compute polynomial and other curvilinear least-squares fits. For examples of OpenOffice spreadsheets that graphs and fits calibration curves, see Worksheets for Analytical Calibration Curves.
12 . Question: What's the difference in using a calibration curve in absorption spectrometry vs other analytical methods such a fluorescence or emission spectroscopy?
Answer : The only difference is the units of the signal. In absorption spectroscopy you use absorbance (because it's the most nearly linear with concentration) and in fluorescence (or emission) spectroscopy you use the fluorescence (or emission) intensity , which is usually linear with concentration (except sometimes at high concentrations). The methods of curve fitting and calculating the concentration are basically the same.
13 . Question: If the solution obeys Beer's Law, is it better to use a calibration curve rather than a single standard?
Answer : It might not make much difference either way. If the solution is known from previous measurements to obey Beer's Law exactly on the same spectrophotometer and under the conditions in use, then a single standard can be used (although it's best if that standard gives a signal close to the maximum expected sample signal or to whatever signal gives the best signal-to-noise ratio - an absorbance near 1.0 in absorption spectroscopy). The only real advantage of multiple standards in this case is that the random errors in preparing and reading the standard solutions are averaged over several standards, but the same effect can be achieved more simply by making up multiple copies of the same single standard (to average out the random volumetric errors) and reading each separately (to average out the random signal reading errors). And if the signal reading errors are much smaller than the volumetric errors, then a single standard solution can be measured repeatedly to average out the random measurement errors.
14 . Question: What is the effect on concentration measurement if the monochromator is not perfect?
Answer : If the wavelength calibration if off a little bit, it will have no significant effect as long as the monochromator setting is left untouched between measurement of standards and unknown sample; the slope of the calibration curve will be different, but the calculated concentrations will be OK. (But if anything changes the wavelength between the time you measure the standards and the time you measure the samples, an error will result). If the wavelength has a poor stray light rating or if the resolution is poor (spectral bandpass is too big), the calibration curve may be effected adversely. In absorption spectroscopy, stray light and poor resolution may result in non-linearity, which requires a non-linear curve fitting method. In emission spectroscopy, stray light and poor resolution may result in a spectral interferences which can result in significant analytical errors.
16 . Question: How can I reduce the random scatter of calibration points above and below the best-fit line?
Answer : Random errors like this could be due either to random volumetric errors (small errors in volumes used to prepare the standard solution by diluting from the stack solution or in adding reagents) or they may be due to random signal reading errors of the instrument, or to both. To reduce the volumetric error, use more precise volumetric equipment and practice your technique to perfect it (for example, use your technique to deliver pure water and weigh it on a precise analytical balance). To reduce the signal reading error, adjust the instrument conditions (e.g. wavelength, path length, slit width, etc) for best signal-to-noise ratio and average several readings of each sample or standard.
17 . Question: What are interferences? What effect do interferences have on the calibration curve and on the accuracy of concentration measurement?
Answer : When an analytical method is applied to complex real-world samples, for example the determination of drugs in blood serum, measurement error can occur due to interferences . Interferences are measurement errors caused by chemical components in the samples that influence the measured signal, for example by contributing their own signals or by reducing or increasing the signal from the analyte. Even if the method is well calibrated and is capable of measuring solutions of pure analyte accurately , interference errors may occur when the method is applied to complex real-world samples . One way to correct for interferences is to use "matched-matrix standards", standard solution that are prepared to contain everything that the real samples contain , except that they have known concentrations of analyte. But this is very difficult and expensive to do exactly, so every effort is made to reduce or compensate for interferences in other ways. For more information on types of interferences and methods to compensate for them, see Comparison of Analytical Calibration Methods .
18 . Question: What are the sources of error in preparing a calibration curve?
Answer : A calibration curve is a plot of analytical signal (e.g. absorbance, in absorption spectrophotometry) vs concentration of the standard solutions. Therefore, the main sources of error are the errors in the standard concentrations and the errors in their measured signals. Concentration errors depend mainly of the accuracy of the volumetric glassware (volumetric flasks, pipettes, solution delivery devices) and on the precision of their use by the persons preparing the solutions. In general, the accuracy and precision of handling large volumes above 10 mL is greater than that at lower volumes below 1 mL. Volumetric glassware can be calibrated by weighing water on a precise analytical balance (you can look up the density of water at various temperatures and thus calculate the exact volume of water from its measured weight); this would allow you to label each of the flasks, etc, with their actual volume. But precision may still be a problem, especially a lower volumes, and it's very much operator-dependent. It takes practice to get good at handling small volumes. Signal measurement error depends hugely on the instrumental method used and on the concentration of the analyte; it can vary from near 0.1% under ideal conditions to 30% near the detection limit of the method. Averaging repeat measurements can improve the precision with respect to random noise. To improve the signal-to-noise ratio at low concentrations, you may consider modifying the conditions, such as changing the slit width or the path length, or using another instrumental method (such as a graphite furnace atomizer rather than flame atomic absorption).
19 . How can I find the error in a specific quantity using least square fitting method? How can I estimate the error in the calculated slope and intercept?
When using a simple straight-line (first order) least-squares fit, the best fit line is specified by only two quantities: the slope and the intercept . The random error in the slope and intercept (specifically, their standard deviation ) can be estimated mathematically from the extent to which the calibration points deviate from the best-fit line. The equations for doing this are given here and are implemented in the "spreadsheet for linear calibration with error calculation". It's important to realize that these error computations are only estimates , because they are based on the assumption that the calibration data set is representative of all the calibration sets that would be obtained if you repeated the calibration a large number of times - in other words, the assumption is that the random errors (volumetric and signal measurement errors) in your particular data set are typical. If your random errors happen to be small when you run your calibration curve, you'll get a deceptively good -looking calibration curve, but your estimates of the random error in the slope and intercept will be too low . If your random errors happen to be large, you'll get a deceptively bad -looking calibration curve, and your estimates of the random error in the slope and intercept will be too high . These error estimates can be particularly poor when the number of points in a calibration curve is small; the accuracy of the estimates increases if the number of data points increases, but of course preparing a large number of standard solutions is time consuming and expensive. The bottom line is that you can only expect these error predictions from a single calibration curve to be very rough; they could easily be off by a factor of two or more, as demonstrated by the simulation "Error propagation in the Linear Calibration Curve Method" (download OpenOffice version).
21. What is the minimum acceptable value of the coefficient of determination (R 2 ) ?
It depends on the accuracy required. As a rough rule of thumb, if you need an accuracy of about 0.5%, you need an R 2 of 0.9998; if a 1% error is good enough, an R 2 of 0.997 will do; and if a 5% error is acceptable, an R 2 of 0.97 will do. The bottom line is that the R 2 must be pretty darned close to 1.0 for quantitative results in analytical chemistry. (c) 2008, 2021 Prof. Tom O'Haver , Professor Emeritus, The University of Maryland at College Park. Comments, suggestions and questions should be directed to Prof. O'Haver at toh@umd.edu.
Number of unique visits since May 17, 2008. Last updated October, 2021