excel generated polynomial equation is wrong

[Guest]

fitting a trendline to data and the polynomial equation it spits out is
obviously wrong. Any ideas? thanks

 

[Bernard Liengme]

It is 'wrong' because the value of y=ax^4+bx^3+cx^2+dx+e if very sensitive
to the values of the confinements (a, b,....)
You could format the trendline and get more precision and then very
carefully copy the values OR you could you LINEST as shown at
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
best wishes

 

[Guest]

I understand what you are saying and fully agree, but that is not the issue.
The equation is just flat wrong UNLESS you set the intercept =0. But doing
that makes the line not fit as well. Any thoughts? Thanks for your time.

 

[Guest]

I apologize- I was wrong- I took every number out to SIX decimal places and
calculated...my answer went from 241 to 94.4 (correct). A bunch of engineers
and we did not think it would make that kind of difference. Thanks for the
help

 

[Bernard Liengme]

Send me (my private email) a sample file with the data.
best wishes

 

[Dana DeLouis]

I took every number out to SIX decimal places and

Hi. It's a known "feature."

Chart trendline formula is inaccurate in Excel
http://support.microsoft.com/kb/211967/en-us

 

[MartinW]

Hi Dana,

The article you linked to says to set your number to 30 decimal
places yet Excel will only display precision to 15 significant digits.

Is this a situation where Excel will calculate to a higher precision
but only display to the limits?
Is it sort of like a cell only displaying 1024 characters even though
it will accept and use a greater amount of data?
Also, if that is the case, what is the limit of decimals.

Thanks
Martin

 

[James Silverton]

Hello, MartinW!
You wrote on Wed, 27 Sep 2006 09:42:22 +1000:

M> The article you linked to says to set your number to 30
M> decimal places yet Excel will only display precision to 15
M> significant digits.

M> Is this a situation where Excel will calculate to a higher
M> precision but only display to the limits?
M> Is it sort of like a cell only displaying 1024 characters
M> even though it will accept and use a greater amount of data?
M> Also, if that is the case, what is the limit of decimals.

From HELP on precision.

15 digit precision Excel stores and calculates with 15
significant digits of precision.

Excel calculates stored, not displayed, values The displayed,
and printed, value depends on how you choose to format and
display the stored value.


James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

 

[Dana DeLouis]

Hi. I think the 30 digits is to allow one to see all the digits using the
basic Number format.
If you format a cell with the Number format, and use the scroll button, you
are limited to 30 digits. I think this is what the article was referring
to.
For example, if you enter the following number:
=123456789012345*(1E-30)
and use the number format out to 30 digits, you will be able to see all the
digits. (Under Number format).
So again, the article seems to suggest using the Number Format, and the
Number format seems limited to 30 Digits. (as seen from the scroll buttons).
What's funny is that the toolbar button "Increase Decimal" can override this
limitation.

 

[MartinW]

Thanks Dana and James,

It was just curiosity on my part. 15 digits provides a lot
more precision than I will ever need.

Regards
Martin