The Comprehensive Calibration Calculator Version 2

A while back I introduced a fancy calibration spreadsheet that allowed for easy comparisons of different curve fits, but mentioned that it wasn’t complete. It didn’t offer force through zero options, and there were some disagreements on r² values when compared to MSD Chemstation. While working on some completely unrelated spreadsheets I had a flash of inspiration on how to force through zero, so I rushed to try it out before my Excel muse abandoned me. You can find the new version here. I also have access to a new chromatography software from Agilent, OpenLab CDS, so I could do some additional comparisons, and the results from that ended up being very interesting.

Before we dig into the software comparisons, let’s talk about what’s new with the spreadsheet. Entering data works the same, but when you look at the ISTD and ESTD templates you’ll see some additional results, as shown in Fig. 1. The linear and quadratic fits now have two sections, Ignore 0 and Force 0, each with equal, 1/x, and 1/x² weighting, so you can compare all options at a glance. There are also two calculations for %RSD on average RF fits, but we’ll get into that later.

Fig. 1 – Curve fit parameters

The residual error section also shows results for all the curve fits, so you again have all results at a glance.

Click image to enlarge.

Fig. 2 – Residual error results

The sample results also have all 13 curve fit types, which can be a bit overwhelming. When I use the spreadsheet, I usually highlight the column of the curve fit I’ve chosen as best, just to keep it clear which results to use. Don’t delete the other columns, since that will break a lot of the calculations.

Click image to enlarge.

Fig. 3 – Sample results

I’ve also added some graphs that show all the curve fits to give a visual idea of how the fits deviate from the calibration points.

Click image to enlarge.

Fig. 4 – Graphs of curve fits

That covers all the new features of the spreadsheet, but the most interesting thing I found was the result comparisons between it, MSD Chemstation, and OpenLab CDS. I’ve added a tab called “Validation” that shows the results, but let’s go over the key points.

First, the %RSD calculations for average RF fits don’t agree between Chemsation and OpenLab. To understand why you have to understand the difference between the standard deviation for populations and standard deviation for samples.

Click image to enlarge.

Fig. 5 – Average RF and %RSD comparison

The standard deviation for a population, stdev.p in Excel, uses the formula:

where is each data point, is the average of the data set, and N is the total data size. This is the calculation to use if you’re calculating the standard deviation for a complete data set. If I measure the height of everyone in a room, this would give me the standard deviation of heights of people in the room, since by measuring everyone I have a complete population.

The standard deviation for samples, stdev.s in Excel, uses the formula

where is each data point, is the average of the data set, and n is the number of samples. This is the calculation to use if you’re measuring an incomplete data set and want to extrapolate to a greater population. If I measure the height of everyone in a single room and want to estimate the standard deviation of heights for the total building, this is the equation to use. Note that this will always give you a greater result from the same data set, since you’re dividing by a smaller number.

Going back to our results, we find that OpenLab uses stdev.p, while Chemstation uses stdev.s. A quick check of some EPA methods finds that SW-846 uses stdev.s, 1633 uses stdev.p, and TO-15A, OTM-45, and a few 500 series methods I checked don’t specify, just telling you to calculate the standard deviation. As for technical reasons to use one or the other, I can see it going either way. If you consider that you’re using every calibration point measured to calculate %RSD, then you could say by using all the calibration points you’ve used a complete population. You could also argue that the calibration points are just a selection of points across the calibration range, so it’s a subset of the infinite number of points across the curve. I’ll leave that up to those more knowledgeable about statistics, but since EPA methods differ in their interpretation, and different software packages from the same company also disagree, I included both calculations so you can choose the most appropriate for your application.

Looking at the ignore 0 curve fits, we see agreement between everything on the curve fits themselves, though Chemstation only reports 4 significant figures, so you have to take into account rounding for that. What’s interesting though is the r² results. My spreadsheet and OpenLab both agree on all r² values, but MSD Chemsation gives higher values for the 1/x and 1/x² weightings, as shown in Figure 6.

Click image to enlarge.

Fig. 6 – Ignore 0 curve fit comparison

It gets even more interesting when we look at the force 0 curves, where there is no agreement at all, regardless of weighting, as shown in Figure 7.

Click image to enlarge.

Fig. 7 – Force 0 curve fit comparison

I’ve mentioned before that r² is a bad metric to use, and the fact that 2 software platforms from the same vendor can’t agree on how to calculate it really drives home how problematic it can be. I’ve kept my r² calculation in the spreadsheet, but I strongly encourage everyone to move to either %RSE or residual error evaluations for curve fits.

I’ll note that if you look out to 8 or 9 significant figures on the curve fit coefficients my spreadsheet and OpenLab start to disagree, but that’s likely just rounding differences between OpenLab and Excel, and it’s so far out that it has no practical effect on the results.

So there you have it, the now very comprehensive calibration calculator, which I hope can help people improve their calibrations and overall data quality. At this point, it does pretty much everything I want it to, so I don’t anticipate any major updates unless there’s strong feedback on something I’m missing.