MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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)?


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,