Linear Regression using the TREND function
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 -- scarlett1 ------------------------------------------------------------------------ scarlett1's Profile: http://www.excelforum.com/member.php...o&userid=17495 View this thread: http://www.excelforum.com/showthread...hreadid=545672 |
Linear Regression using the TREND function
scarlett1 -
Specifically, what is the part of the explanation you do not understand? - Mike www.mikemiddleton.com "scarlett1" wrote in message ... 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 -- scarlett1 ------------------------------------------------------------------------ scarlett1's Profile: http://www.excelforum.com/member.php...o&userid=17495 View this thread: http://www.excelforum.com/showthread...hreadid=545672 |
Linear Regression using the TREND function
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 "scarlett1" wrote: 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 -- scarlett1 ------------------------------------------------------------------------ scarlett1's Profile: http://www.excelforum.com/member.php...o&userid=17495 View this thread: http://www.excelforum.com/showthread...hreadid=545672 |
All times are GMT +1. The time now is 08:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com