Linear Regression using the TREND function

[scarlett1]

Im setting up a calibration graph for an analytical method and need to
work out a linear regression calculation so that I can calculate the
concentration of an unknown.

I have two columns within the calibration graph and want to have a
separate area where I input a value and the concentration is calculated
from the linear regression equation. Ive got the equation onto a chart
and know I need to use the TREND function. I have looked at the excel
explanation but dont really understand it. Can someone explain it
simply? and how I need to set up my data entry? thanks

 

[Mike Middleton]

scarlett1 -

Specifically, what is the part of the explanation you do not understand?

- Mike
www.mikemiddleton.com

 

[Jerry W. Lewis]

Analytical method calibration typically involves the concentration of an
analyte and an instrument response corresponding to that concentration.

Your first decision is which to use as X and which to use as Y in the linear
regression. A basic assumption of least squares regression (which is what
Excel does) is that X is measured without error -- while this may be true for
the concentrations that produce your calibration curve, it is never true for
the instrument response. As a result, classic calibration takes
concentration to be the X variable, in which case the TREND function has no
bearing on the problem.

With concentration as the X variable then you need the slope and intercept
extimates (Excel's SLOPE and INTERCEPT functions or LINEST) so you can
estimate the unknown concentration from a response by
conc = (response - intercept)/slope.

For TREND to come into play, you have to accept Krutchkoff's (Technometrics
10:811-823, 1968) argument that using response as the X variable gives you a
smaller mean square error for your quantitations than does classical
calibration, despite the fact that the estimation approach is necessarily
biased. 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,
thererfore I am unconvinced that they have adequately understood or even
thought about what they are doing.

Jerry