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

See Bernard Liengme's
http://www.stfx.ca/people/bliengme/E...Polynomial.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <acd501c4369e$0bf9f600$(E-Mail Removed)>,
(E-Mail Removed) says...
> 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

Frans wrote:

> 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

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
>-----Original Message-----
>See Bernard Liengme's
>http://www.stfx.ca/people/bliengme/E.../Polynomial.ht
m
>
>--
>Regards,
>
>Tushar Mehta
>www.tushar-mehta.com
>Excel, PowerPoint, and VBA add-ins, tutorials
>Custom MS Office productivity solutions
>
>In article <acd501c4369e$0bf9f600$(E-Mail Removed)>,
>(E-Mail Removed) says...
>> 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
>>
>>
>.
>

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

(E-Mail Removed) wrote:

> 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
>
>>-----Original Message-----
>>See Bernard Liengme's
>>http://www.stfx.ca/people/bliengme/E...Polynomial.htm
>>
>>Tushar Mehta