Resource Hub / ChromaBLOGraphy / Introducing the Comprehensive Calibration Calculator

Introducing the Comprehensive Calibration Calculator

16 May 2024

Recently Colton Myers has released a standard calculation spreadsheet to pair with our calibration blog series. I didn’t want to be left out, so here I am introducing a spreadsheet that has almost everything you’ll need to calculate a good calibration. I can’t take full credit for this, as the initial form of the calculator came from the Arizona Department of Health Services (original link). From there, I managed to fix some of the weighted quadratic fit issues that were originally present in the sheet and added some extra calculations for %RSE and residual error. Also, Colton managed to figure out some Excel wizardry with cell references to make it much more user friendly. You can find the Restek version of this spreadsheet here. The spreadsheet was updated on 7/3/24 to correct a calculation error.

Despite all the work we’ve put into this, there are still some limitations to it. First, I haven’t set up any force through zero fits yet, though I hope to add that in the future. The original spreadsheet has those as an option if you need them, though be aware that the weighted quadric fits in the Arizona Department of Health Services version don’t seem to match what I get from MSD Chemstation. Also, I can’t get the r2 values for weighted quadratic fits to match MSD Chemstation, though the fits themselves agree. However, I don’t see this as a big problem, since per my previous blog, there are better metrics to use for evaluating calibration curves.

Instructions are on the first tab of the spreadsheet, but with the work that Colton did it’s very easy to use. Simply paste your compound names and responses into the Cal Data tab, which supports up to 200 compounds. There are other fields there for data file info and retention times, but they’re not needed for the spreadsheet to work, they are included to help with traceability. Next, at the bottom of the Cal Data Formatted tab, add in your calibration concentrations. After that, make as many copies of either the ISTD or ESTD template tabs (for internal and external standard calibrations respectively) as you have compounds. On each tab enter the name of your compound and internal standard (if applicable) exactly as they are entered in the Cal Data tab. By default, it’s set up for 10 calibration levels, but if you have less just delete the rows below your last cal level. If everything has been entered correctly the spreadsheet will give you equal weighted, 1/x, and 1/x2 fits for linear and quadratic regressions, as well as average RF, with associated r2, %RSE, and residual error data. Test data is initially present for compounds 99 and 100 as an example.

Figure showing the calibration calculator spreadsheet.

You can also use the spreadsheet to look at sample results based on the calibrations it calculates. Simply copy the data into the Sample Data tab, same as was done for the Cal Data. After that it will give results for each calibration curve type at the bottom of the ESTD or ISTD tabs.

Figure showing the calibration calculator spreadsheet.

Regulated labs may be required to validate any software or calculations used; in which case it can still be handy to use this spreadsheet to initially evaluate different curve fits before properly setting up your instrument software. I find it much more convenient to compare curve fits with this spreadsheet than with most instrument software, which usually don’t have a good way to compare fits side-by-side.

Hopefully this proves to be a useful tool for labs. One of our goals at Restek is to make chromatography easy, and now it’s easier than ever to be as picky about calibrations as I am!