Replicating ChemStation Calibration Curves in Excel?

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

7 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!]
That's an excellent explanation, and it totally makes sense. Thanks very much!

As a follow-up: does anyone have a good way to replicate the calculated concentration in Excel, given the ChemStation-reported calibration curve data and area counts? That is, given the quadratic info (a, b, and c, maybe residual) and area counts, can anyone replicate the formula to do these calculations in Excel? For developmetnal work, it is much, much faster to do it outside the system, especially when trying to tweak a calibration curve.

(as a real-data example: given the curve data and sample areas below (taken from ChemStation), can anyone make Excel spit out the same calculated concentrations?

a: 480753.796526364
b: 115210.039428178
c: -519.6174309
Residual: 381.06051

Area - concentration
1953.67602539063 - 0.01983
3104.93359375000 - 0.02815
4432.73876953125 - 0.03721
14.7286500930786 - 0.00455
That's an excellent explanation, and it totally makes sense. Thanks very much!

As a follow-up: does anyone have a good way to replicate the calculated concentration in Excel, given the ChemStation-reported calibration curve data and area counts? That is, given the quadratic info (a, b, and c, maybe residual) and area counts, can anyone replicate the formula to do these calculations in Excel? For developmetnal work, it is much, much faster to do it outside the system, especially when trying to tweak a calibration curve.

(as a real-data example: given the curve data and sample areas below (taken from ChemStation), can anyone make Excel spit out the same calculated concentrations?

a: 480753.796526364
b: 115210.039428178
c: -519.6174309
Residual: 381.06051

Area - concentration
1953.67602539063 - 0.01983
3104.93359375000 - 0.02815
4432.73876953125 - 0.03721
14.7286500930786 - 0.00455
ok, it's a bit of a faff, but here's how to do it:
first calculate "q":
=-0.5*(bb+SQRT(POWER(bb,2)-(4*aa*(cc-A4))))
In Excel, A4 is the area, and aa, bb, and cc are named ranges pointing at the cells containing your calibration curve constants. You can just use the format $F$6 or whatever, but I prefer to select the cell and type "aa" (or whatever) in the name box at the top left to create a named range, which I can refer to more easily in formulae. I don't use "a" because when you reach "c" you'll find it has a reserved meaning and can't be used as a name.
Note that this version of "q" applies only if the constant b is positive.
Then calculate (cc - Area)/q
(you can point to wherever you've calculated q. In my case the actual formula is
=(cc-A4)/D4
(for completeness, the other solution to the quadratic curve, which you don't want, would have been q/aa)
Thanks, lmh...that works a treat!

I've condensed it into a single cell formula, too:

=(cc - area)/(-0.5*(bb+SQRT(POWER(bb,2)-(4*aa*(cc-area)))))
Are there any texts that go in details about this formula and how to calculate concentrations including description of some pitfalls.
7 posts Page 1 of 1

Who is online

In total there are 2 users online :: 1 registered, 0 hidden and 1 guest (based on users active over the past 5 minutes)
Most users ever online was 1117 on Mon Jan 31, 2022 2:50 pm

Users browsing this forum: Google [Bot] and 1 guest

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 & Beverage, Environmental, (Bio)Pharmaceutical, Bioclinical, Liquid Chromatography, Gas Chromatography and Mass Spectrometry.

Liquid Chromatography

Gas Chromatography

Mass Spectrometry