calibration lines, calculation

  • Thread starter Thread starter Frans
  • Start date Start date
F

Frans

I'm using excel for my calibration lines. Excel can give
the formula of the line. I use this formula to calculate
the unknow values. I have to put the formula in a cell.
This is no problem with lineair calibration lines but it
because more difficult with other lines of other orders.
Is there a simple command or formula in excel to calculate
unknow values using the formula of the calibration line
excel gives?

Thanks,
Frans
 
For a quadratic, you can use the quadratic formula to solve for x in
terms of y, given the coefficients. For higher order polynomials, and
other functional forms, you may have to settle for numeric inversion of
the relationship. You can use Solver to find the unknown x given the
measured y.

Another approach to consider is to simply reverse the roles of y and x,
and eliminate the inversion problem entirely. Least squares implicitly
assumes that the uncertainty in x observations is negligible compared to
the uncertainty in the y observations. That with some regularity
conditions (independence and equal variances) leads to the conclusion
(Gauss-Markov theorem) that least squares gives you the best linear
unbiased estimates of the coefficients, and therefore the best linear
unbiased predictions of y. Purists often take this to imply the best
choice of x and y for calibration. However in calibration you want an
equation to predict x given y, not y given x. Reversing the role of x
and y may seem slightly sleazy, but Krutchkoff (Technometrics
10:811-823, 1968) contended that it gives you a smaller mean square
error for your quantitations than does classical calibration.
Krutchkov's paper was controversial at the time, but in the following
decade the use biased shrinkage estimators to reduce mean squared error
in other contexts became more fashionable.

My experience suggests that lab instrument software is now fairly evenly
split between those that do classical calibration and those that reverse
the role of x and y. However the documentation for many instruments
that reverse the role of x and y, erroneously claim to do classical
calibration.

Jerry
 
I've looked at the page and I found the cubic equation
solver item....the formula's given helped me a lot but I
have a problem. It only counts for A=1, in my equations A
doesn't. Making a chart is no problem and excel can give
the equation and r2 of the polynomal after I made the
chart. (scatter,trendline, trendline options) Do you have
suggestions? Information?
I want to calculate X with a given Y. By placing Y to the
right, I have to calculated the roots of the equation....

Regards,
Frans
 
Just divide all the coefficients by A to put a cubic in standard form.
If a*x^3+b*x^2+c*x+d=0 then so does x^3+(b/a)*x^2+(c/a)*x+(d/a)=0.

Jerry
 
Back
Top