Quadratic regression

[shaun78]

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...

[LiVD]

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)

[shaun78]

Well … I've now become exceptionally curious as to exactly what Excel things it is doing...

[bunnahabhain]

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.

[shaun78]

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...

[bunnahabhain]

What version of excel are all of you using? I'm on 2003...

Excel 2010. I really MISS Office 2003!

[dblux_]

Excel 2010. I really MISS Office 2003!

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

[Jasn]

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.

[dblux_]

...
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.

[Jasn]

Yeah, my mistake

[shaun78]

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.

[bunnahabhain]

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

[shaun78]

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.

[dblux_]

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.

[Yama001]

Is Empower perhaps swapping the variables? I believe one of our systems does it that way - incorrect statistically.