Linest, trendlines and chart problem. Need help!

Posted by Corey Dale on February 13, 2002 3:43 PM

I have a quick question.

I have a set of 4 data points on a chart, with a 4th order polynomial trendline connecting the points. When plotted, the trendline looks like a bell curve.

I've used linest to give me the coefficients of the trendline equation, and then I used the Solver add-in to find the maximum y-value and the corresponding x- value.

My problem is this: When I choose to have the trendline equation shown on the chart, the 4th order trendline is only showing a 3rd order equation. I need to know if the values I get using LINEST are accurate, or if the trendline is not plotting correctly.

When I solve the equation, the numbers look good (they are which I'd expect to get), but the trendline doesn't plot right. In fact, the X and Y values I get using Solver don't even fall on the trendline as it is shown on the chart.

This is an example of the LINEST data I am using:

=LINEST(N26:N31, O26:O31^{1,2,3,4})

This gives me 5 coefficients for the equation.

Any ideas as to which might be wrong (calculated values or the way the trendline has plotted)?

Thanks,
Corey

Posted by Corey Dale on February 13, 2002 4:25 PM

Just tried solving (using Linest and Solver) for the same set of data, this time using a 3rd order polynomial instead of the forth [ ie. {1,2,3} in the LINEST function]. The coefficients matched the 3rd order trendline equation which Excel put on the chart, and the solution matched the plotted trendline (fell on the trendline).

So, even though I asked Excel to plot the 4th order polynomial trendline, it decided to only plot a 3rd order. Is this a bug in Excel? Why does it revent back to a 3rd order each time? I find that a fourth order better fits the data for my purposes.

Any ideas?

Thanks again,
Corey