Replicating ChemStation Calibration Curves in Excel?

Discussions about chromatography data systems, LIMS, controllers, computer issues and related topics.

2 posts Page 1 of 1
Because of the way my company has ChemStation set up, it is a tedious and slow process to use ChemStation to perform calculations against the quadratic calibration curve. I would much rather export my data to Excel, and run the numbers there, but I'm not quite able to replicate ChemStation's results.

I'm able to replicate the equation that ChemStation generates (that is, the y = ax^2 + bx + c, as well r), by using the Excel array formula
=LINEST([peak area],[curve point concentrations]^{1,2},TRUE,TRUE)
to generate a, b, and c. This LINEST function gives me the exct a, b, and c that Chemstation spits out in the calibration report.

However, when I then try to use a, b, and c to determine the concentrations of my sample, I get results that are wildly, WILDLY out of line with what ChemStation calculates. (The Excel formula I'm using is
=(a * ([area]^2) ) + (b * [area]) + (c)
where a, b, and c are derived from the LINEST function above). But, if I change the LINEST function, and swap the x and y to
=LINEST([curve point concentrations],[peak are]^{1,2},TRUE,TRUE)
I get very different a, b, anmd c, but back caluclating for sampel concentrations I get very, very close to what ChemStation calculates...but not exact. Even using all 14 available decimals, my calculations vary from what ChemStation calculates.

Does anyone have any insight that they could share?
Yes. What's happening is this:

Typically a quadratic calibration curve assumes that the concentration is known exactly, and the errors are in the signal. Therefore signal is plotted as Y and concentration as X, and the calibration curve is y=aX^2+bX+c, errors in a vertical direction. This is what Chemstation does, and it's what your first linest() fit does.

But when you want to use this calibration curve to calculate concentrations, the thing you've measured is Y (not X) and you're trying to calculate X. Therefore you can't just substitute the value into aX^2+bX+c as you have. You're reading the calibration curve the wrong way round.

Actually you have to solve the quadratic equation for X, given Y. You can use the classic equation for the purpose (x = -b +/- square root of b^2-4ac etc. etc.) but (1) it's numerically bad practice because quite often -b + square-root of b-squared degenerates to nearly zero; subtracting two very large numbers is a superb way to lose numerical precision; there is a better approach after rearranging formulae - google or look in Numerical Recipes if you have a copy to hand; and (2) the formula has two possible answers, so you've got to choose the correct one.

What you did in your second linest() fit was to fit the curve the other way round, using signal as the x-axis and concentration as the y-axis, in which case, yes, you'll get a sensible answer from plugging the results into y=ax^2+bx+c (though the constants a, b, and c will now be totally different to chemstation's). The problem is that you're now doing your fit the other way round; the error is assumed to be in the concentration, not the signal. For perfect data, it would make no difference, but if the data don't exactly fit the quadratic curve, the fit will now be slightly different, which is why you have slightly different results to Chemstation's. It's not a brilliant way to do the fit, because the least-squares best fit should be minimising the error in the same direction as the error occurs. If the uncertainty is mostly in the Y-direction, a vertical error-bar is a good measure of the uncertainty of the point, and least-squares regression minimising the distances between the line and point in a Y-direction is the best representation of what's really happening.

This is precisely why CDS software such as Chemstation is so convenient. Handling quadratic fits the wrong way round in Excel is a bit of a faff and liable to typing-errors. It's also less flexible because you can't weight the points, should you wish to do so.

[an aside: Shimadzu, incidentally, give the capacity to do PDA calibration curves either way round (the choice of what is plotted on the x-axis determines not only what the graph looks like, but also how it is fitted). Their MS data have concentration on X-axis, while PDA defaults to signal on X-axis. This looks odd at first sight, but is probably theoretically correct: in MS, the standard concentrations are probably more precise than the signal, while in PDA, which is so very reliable, it is quite possible that the PDA is much better than my pipetting!]
2 posts Page 1 of 1

Who is online

In total there are 7 users online :: 0 registered, 0 hidden and 7 guests (based on users active over the past 5 minutes)
Most users ever online was 599 on Tue Sep 18, 2018 9:27 am

Users browsing this forum: No registered users and 7 guests

Latest Blog Posts from Separation Science

Separation Science offers free learning from the experts covering methods, applications, webinars, eSeminars, videos, tutorials for users of liquid chromatography, gas chromatography, mass spectrometry, sample preparation and related analytical techniques.

Subscribe to our eNewsletter with daily, weekly or monthly updates: Food, Environmental, (Bio)Pharmaceutical, Bioclinical, Liquid Chromatography, Gas Chromatography and Mass Spectrometry.

Liquid Chromatography

Gas Chromatography

Mass Spectrometry