Quadratic regression

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

19 posts Page 1 of 2
HI all....

I've got a method where I need to use quadratic regression as the fit type for a calibration. A 5 point calibration curve is generated using the following points:

Code: Select all

Concentration (ug/mL)       Peak Area
50                                396287
71                                717880
107                                1363599
125                                1757618
143                                2247428


If I setup a quadratic fit in Empower, I generate the following equation for a best fit line:

79.2x^2 + 4400x - 11600

If I use Excel to generate an equation for the best fit line, the following is produced:

82.407x^2 + 3874.2x + 5919.9

I get the closeness of results on both the x^2 and x constant. I am confused at the difference in intercept as well as the total sign change. Clearly, results calculated using the two different approaches are not exactly the same; or even close for that matter.

For what it is worth, I can calculate the regression line on my TI-89, by hand, using Maple, and using Mathematica and I get the same equation that Empower generated.

My question is: why is there a difference? What is Empower et al doing differently that Excel is not?

TIA...
With your data, this is what excel gives me:

79.1680518x²+4398.320398x-11612.90427

I used the formula: =LINEST(C4:C8;B4:B8^{1,2};1;1)
Well … I've now become exceptionally curious as to exactly what Excel things it is doing...
LiVD wrote:
With your data, this is what excel gives me:

79.1680518x²+4398.320398x-11612.90427


Me too. Generated by the plot tool, trendline polynomial 2nd order.
Image

Using both chart functions and linest. Because I am making a remote connection from a mac into my work PC, I can't build the nested function to get anything other than the X^2 constant when using linest. You can see that it is in agreement with the chart function…

What version of excel are all of you using? I'm on 2003...
shaun78 wrote:
Image
What version of excel are all of you using? I'm on 2003...


Excel 2010. I really MISS Office 2003!
Excel 2010. I really MISS Office 2003!


But Excel 2010 (rel. 14.0) gives unsurprisingly the same result as Excel 2003 (rel. 11.0) !!!
Image

Image
I am getting the same results as the other 2 people.

79.168x² + 4398.3x - 11613.

This looks the same to me as what empower calculated for you given that empower only uses 3 significant digits to display the equation (at least that's what i think)

Regarding the question about what Excel is doing wrong: It is possible that Excel gives 'wrong' results when regressions (or other calculations for that matter) get more and more complicated. This is due to 2 reasons.

1) binairy system
2) significant digits in Excel

1) Excel uses 8bit variables to store numbers in. Since most decimal numbers are represented as a never ending chain of 0's and 1's it is impossible to describe the numbers exactly in a 8bit binairy.
2) Excel only uses 15 significant digits

In my opinion these do not explain the difference you are seeing since other people find other results. It is possible that the version might have something to do with it. It makes me cautious though for blindly trusting Exels results.
Jasn wrote:
...
1) Excel uses 8bit variables to store numbers in. Since most decimal numbers are represented as a never ending chain of 0's and 1's it is impossible to describe the numbers exactly in a 8bit binairy.
2) Excel only uses 15 significant digits

In my opinion these do not explain the difference you are seeing since other people find other results. It is possible that the version might have something to do with it. It makes me cautious though for blindly trusting Exels results.


Under normal circumstances, Excel always stores and manipulates numbers as 8-byte "Double Precision Floating Point" numbers, or "Doubles".

8-byte not 8-bit - feel the difference? :)

I hope that shaun78 will eventually find out what went wrong with his/her calculations with Excel.
Yeah, my mistake ;)
At this point it seems that everything seems to be pointing to "there is something wrong with my installed version of Excel". The same installed version on other computers gives the same correct Empower result.

Unfortunately, this is fairly disturbing. I'm going to see if I can't get IT to uninstall and reinstall Office and see if that does not fix the problem.

This just adds fuel to my fire that people should not be performing calculations outside of the CDS, which is what I was at a remote location last week trying to convince people of.
Stupid question: Did you copy the data into excel or type by hand? Sometimes, and I don't know of any rule that is followed by excel, it treats numbers (well, what is obviously a number to humans) as dates or text or weird microsofts. This will yield "wrong" results when performing calculations.
Try to type the numbers by hand, just for fun!

Jörg
I have both copied the data and retyped it. I've gotten the same result both ways.

I had IT uninstall and reinstall Office yesterday.

That did not fix the problem. I'm fairly puzzled over the cause of the problem anymore.
shaun78 wrote:
I have both copied the data and retyped it. I've gotten the same result both ways.

I had IT uninstall and reinstall Office yesterday.

That did not fix the problem. I'm fairly puzzled over the cause of the problem anymore.

You will ruin Intel if you find another pentium bug. :)
Is Empower perhaps swapping the variables? I believe one of our systems does it that way - incorrect statistically.
19 posts Page 1 of 2

Who is online

In total there are 15 users online :: 0 registered, 0 hidden and 15 guests (based on users active over the past 5 minutes)
Most users ever online was 599 on Tue Sep 18, 2018 9:27 am

Users browsing this forum: No registered users and 15 guests

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

Liquid Chromatography

Gas Chromatography

Mass Spectrometry