SOLVED

How to create a custom trendline?

Copper Contributor

When none of the trendline options (exponential, linear, logarithm, polynomial, power) fit well, can a different formula be used?  If not, can the same be achieved by plotting a custom formula with coefficients adjusted to minimise R-squared?

4 Replies

@Ian_Heath If you have a formula that you think works why not just calculate the corresponding points on the sheet and then add it to the graph?

@mtarler As you suggested, I could just plot the trendline manually, however, I would still need to calculate the coefficients of the formula to minimise R-squared.  Maybe I could use LINEST or something similar to do this.  

best response confirmed by Ian_Heath (Copper Contributor)
Solution

@Ian_Heath so the basic answer is that you can't input your own 'base' equation for excel to use for the trend line.  That said a number of the options do give you the option to set the intercept which can help or change particular characteristics like power of the polynomial.  If you have a completely different form then I would set it up as an equation that uses the parameters set in fixed cells and then add another column to calculate the R^2 value for each point and then the sum so you can use the solver (Data -> what-if analysis -> Goal Seek...) to find at least 1 parameter based on minimizing that R^2.

@mtarler 

That's the conclusion I had come to previously, including the use of Solver too!  My post was to try to see if there was a cleverer way of doing it using trendlines.  It seems from your response that there isn't.  Thanks for you help.

 

Ian

1 best response

Accepted Solutions
best response confirmed by Ian_Heath (Copper Contributor)
Solution

@Ian_Heath so the basic answer is that you can't input your own 'base' equation for excel to use for the trend line.  That said a number of the options do give you the option to set the intercept which can help or change particular characteristics like power of the polynomial.  If you have a completely different form then I would set it up as an equation that uses the parameters set in fixed cells and then add another column to calculate the R^2 value for each point and then the sum so you can use the solver (Data -> what-if analysis -> Goal Seek...) to find at least 1 parameter based on minimizing that R^2.

View solution in original post