Feb 01 2021 04:03 PM
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?
Feb 01 2021 04:50 PM
@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?
Feb 02 2021 07:18 AM - edited Feb 02 2021 07:19 AM
@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.
Feb 02 2021 07:46 AM
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.
Feb 02 2021 09:10 AM
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
Feb 02 2021 07:46 AM
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.