I am using an XY scatter plot. I have applied a polynominal trendline to the
data. The trendline appears to be visually correct.

However, the displayed equation does not match the plotted trendline!
Sometimes it is obvious, the order isn't even correct. Other times the
coefficients are simply incorrect (which is worse). It is present on several
charts, although they are within the same workbook.

There are numerous reports of this on the internet, but I do not see a fix.
I do have SP1 installed. The document was created in Excel 2007, but as a
2003 workbook. The same issue is present on multiple computers with Excel
2007.

The equation however is correct in Excel 2003.

This strikes me as a major bug (assuming it isn't something I am doing
incorrectly) that is likely patched.

Thanks,
Dan

Under some conditions, Excel 2007 loses the leading coefficient with
polynomial fits. The development team know about this and a hotfix should
appear soon.
Use LINEST to get the values (see
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"DanB" wrote in message
...
I am using an XY scatter plot. I have applied a polynominal trendline to
the
data. The trendline appears to be visually correct.

However, the displayed equation does not match the plotted trendline!
Sometimes it is obvious, the order isn't even correct. Other times the
coefficients are simply incorrect (which is worse). It is present on
several
charts, although they are within the same workbook.

There are numerous reports of this on the internet, but I do not see a
fix.
I do have SP1 installed. The document was created in Excel 2007, but as a
2003 workbook. The same issue is present on multiple computers with Excel
2007.

The equation however is correct in Excel 2003.

This strikes me as a major bug (assuming it isn't something I am doing
incorrectly) that is likely patched.

Thanks,
Dan

Interesting. The workaround will work, but isn't much fun for workbooks with
numerous plots of odd data ranges. To display the equation on the chart a
text box has to be manually created.

Thanks for the advice though.

Are the conditions necessary for this published (so that they can be
avoided)? I am shocked such a major bug (displaying an incorrect equation
seems major) has gone unresolved for this long. I imagine complaining isn't
going to help though.

Thanks,
Dan

"Bernard Liengme" wrote:

Under some conditions, Excel 2007 loses the leading coefficient with
polynomial fits. The development team know about this and a hotfix should
appear soon.
Use LINEST to get the values (see
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"DanB" wrote in message
...
I am using an XY scatter plot. I have applied a polynominal trendline to
the
data. The trendline appears to be visually correct.

However, the displayed equation does not match the plotted trendline!
Sometimes it is obvious, the order isn't even correct. Other times the
coefficients are simply incorrect (which is worse). It is present on
several
charts, although they are within the same workbook.

There are numerous reports of this on the internet, but I do not see a
fix.
I do have SP1 installed. The document was created in Excel 2007, but as a
2003 workbook. The same issue is present on multiple computers with Excel
2007.

The equation however is correct in Excel 2003.

This strikes me as a major bug (assuming it isn't something I am doing
incorrectly) that is likely patched.

Thanks,
Dan

Hi Dan,

Bernard has told you how to do what you asked for. Just a word of caution on
using polynomial regression.

I don't know what data you are trying to fit, but I usually discourage
people from using polynomial models unless they have a good reason to
suspect that the 'real life' data should fit a polynomial curve (e.g. a ball
thrown in the air).

If you are trying to fit business data, then polynomial curves are not very
useful IMO. Given a fixed number of data points, you can always find a
polynomial curve with exact fit just by adding enough coefficients. However,
this is not likely to be a good model for the underlying data.

If you have monthly data that may show a seasonal component and a trend,
then it may be better to try smoothing the data with a logarithmic filter
that does not remove too much of the underlying structure. You can download
a Henderson filter from
http://www.edferrero.com/ExcelCharts...2/Default.aspx

HTH

Ed Ferrero
www.edferrero.com

Hi Ed,

I appreciate your advice.

As always, I am skeptical of trend fits. We are fitting a trend to
experimental, which looks quadratic (or cubic), and are using it to
interpolate between points, and not predictions. I did not notice the
equation was incorrect until I took the derivative and it did not match the
plotted results.

I agree that people go nuts with higher order polynomial terms, which can
end up with good R^2 values, but meaningless results.

Using computer results blindly is very dangerous, and I am glad to see that
you are discouraging it.



"Ed Ferrero" wrote:

Hi Dan,

Bernard has told you how to do what you asked for. Just a word of caution on
using polynomial regression.

I don't know what data you are trying to fit, but I usually discourage
people from using polynomial models unless they have a good reason to
suspect that the 'real life' data should fit a polynomial curve (e.g. a ball
thrown in the air).

If you are trying to fit business data, then polynomial curves are not very
useful IMO. Given a fixed number of data points, you can always find a
polynomial curve with exact fit just by adding enough coefficients. However,
this is not likely to be a good model for the underlying data.

If you have monthly data that may show a seasonal component and a trend,
then it may be better to try smoothing the data with a logarithmic filter
that does not remove too much of the underlying structure. You can download
a Henderson filter from
http://www.edferrero.com/ExcelCharts...2/Default.aspx

HTH

Ed Ferrero
www.edferrero.com

What conditions are we talking about? Several of us here use polynomial data
fits with Excel, though for the most part, I calculate them myself.

--
I speak for truth, enlightenment and justice, but not for the US Air Force.



"Bernard Liengme" wrote:

Under some conditions, Excel 2007 loses the leading coefficient with
polynomial fits. The development team know about this and a hotfix should
appear soon.
Use LINEST to get the values (see
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"DanB" wrote in message
...
I am using an XY scatter plot. I have applied a polynominal trendline to
the
data. The trendline appears to be visually correct.

However, the displayed equation does not match the plotted trendline!
Sometimes it is obvious, the order isn't even correct. Other times the
coefficients are simply incorrect (which is worse). It is present on
several
charts, although they are within the same workbook.

There are numerous reports of this on the internet, but I do not see a
fix.
I do have SP1 installed. The document was created in Excel 2007, but as a
2003 workbook. The same issue is present on multiple computers with Excel
2007.

The equation however is correct in Excel 2003.

This strikes me as a major bug (assuming it isn't something I am doing
incorrectly) that is likely patched.

Thanks,
Dan