Adding a Polynomial Trendline Using Linest

[danielexcelvba]

Hi everyone,

I've been trying to generate a decent trendline for a graph that requires non-linear regression not available in excel. My idea was to add a cubic polynomial trendline that would fit the data well, but only if I exclude certain points. I manager to find the formula for this curve using LINEST, and I'm wondering if there is any way I can add a custom trendline to my chart using the formula.

Thanks in advance,
Daniel

[MrShorty]

Here's how I do it (note that almost all of the work is in the spreadsheet and not in the chart):

1) Enter LINEST() function in the spreadsheet (a cubic polynomial will require a horizontal array of 4 cells, unless you are also outputting the additional statistics).
2) Add a column of X values spanning the desired range of x values on the chart.
3) Add a column adjacent to those X values to compute the regressed Y values (some form of =Ax^3+Bx^2+Cx+D with appropriate references to the LINEST() output and the column of X values.
4) Go into the chart -> Select Data -> New Series -> select the appropriate ranges for X values (step 2) and Y values (step 3).

[danielexcelvba]

That makes a lot of sense! Thanks a lot