Errors in Excel trend lines.

[Bob_Irving]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am finding that the polynomial trendlines in charts displaying erroneous equations. When I try to use the equations in the sheet to create a new graph, I get a different line from the trend line! If I then modify the new curve to match the original trend line and create a trendline to fit that, I get a correct equation.... Along with the last query I posted, this is becoming a nightmare and I will have to give Office on Mac to retain my sanity.

 

[JE McGimpsey]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am finding that the polynomial trendlines in charts displaying erroneous
equations. When I try to use the equations in the sheet to create a new
graph, I get a different line from the trend line! If I then modify the new
curve to match the original trend line and create a trendline to fit that, I
get a correct equation.... Along with the last query I posted, this is
becoming a nightmare and I will have to give Office on Mac to retain my
sanity.

I can't reproduce that here - can you post a reply with a simple data
set that shows this? (along with the parameters you enter to create the
chart).

 

[Bob_Irving]

Not sure I can... I just tried it with a simple sheet and it doesn't happen!

I've got a workbook with just one datasheet and one duff chart in it. Is there a way I can send that to you?

 

[Mike Middleton]

Bob_Irving -

Maybe you're not using enough significant digits for the coefficients. One
workaround is to increase the number of digits shown in the trendline
equation in the text box on the chart. A better way, usually, is to
calculate the coefficients using worksheet formulas. See
http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

- Mike
http://www.MikeMiddleton.com

 

[JE McGimpsey]

Not sure I can... I just tried it with a simple sheet and it doesn't happen!

I've got a workbook with just one datasheet and one duff chart in it. Is
there a way I can send that to you?

Reply to this with the name of the file you're sending, and send it to
my email address on this post. I'll set my filters to allow it through.

 

[Bob_Irving]

It'll be called test trend.xls

 

[Carl Witthoft]

I'll second Mike's suggestion. Between LINEST and LOGEST you can fit
an awful lot of stuff.
If you want to get deeper than that (i.e. into generalized nonlinear
fits), it's time for R or MatLab
Carl

 

[Bob_Irving]

No, polynomial functions are subtle enough for me! I'll go with the LINEST solutions.... Still doesn't excuse the trend line's inaccuracy though!

 

[Mike Middleton]

Bob_Irving -

Regarding your reference to "trend line's inaccuracy," please provide an
example.

As far as I know, Excel's trendlines are accurate, and you can make full use
of fifteen digits of precision if you choose to format the equation display.
But it's usually easier to use worksheet functions for the calculations.

Of course, the higher the order of the polynomial, the more important it is
to use more significant digits in calculations. In general, there is seldom
a reason to use more than polynomial of order 3 (a "cubic" equation). And
it's important to avoid overfitting.

- Mike
http://www.MikeMiddleton.com

 

[Bob_Irving]

By all means, I will provide an example - I sent one to J E McGimpsey, but it seems to have got stuck in his e-mail system.

I don't think I'm stressing this facility too far - I'm only using square functions - anything higher doesn't have any significance - the coefficients are too small. The trend line equations are usually to 4 dec. places.

How can I pass over an example?

 

[Mike Middleton]

Bob_Irving -

You wrote "The trend line equations are usually to 4 dec. places."

With polynomial of order 2 ("quadratic" equation), four decimal places is
usually not sufficient for replicating values of the fitted equation. The
actual coefficients have 15 significant digits. Use them.

As I wrote in a previous newsgroup message in this thread, "Maybe you're not
using enough significant digits for the coefficients. One workaround is to
increase the number of digits shown in the trendline equation in the text
box on the chart."

One way to show more digits: When the trendline equation is initially
displayed, select the text box (a single click). Then repeatedly click the
Increase Decimal button.

- Mike
http://www.MikeMiddleton.com