@ > Home > Contents > Use Chart trend line formula as cell formula Excel 97+


Use the formula provided by "chart trend line" in a cell.


In A1:B8 the X and Y values be typed in.

On this selection, create a X-Y-chart.

Right-click on a data point of the chart,
   "add trend line",
      choose a suitable "Type",
      "Options": [X] show equation in chart

Right-click into the equation,
   "Format data labels"
      "Numbers" number 15 decimals

Copy equation (not its text box) into a cell (C2).

D2: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"x","*$A2^"),"^ ",),"y ",)

Copy D2 and insert its value on itself. F2-Enter converts the text to a formula. Thomas Ramel has an alternative: Edit Replace "=" with "=".

Copy the formula along the data. Compare it with Y data.

VBA recording is an alternative for frequent changing trend formulas. However, it does not seem quite easy to adjust the macro to your needs ("Automation Error").

The more decimals you provide, the less accuracy of the trend will suffer when using high degree polynomials and/or extrapolation on far X values. 

See also: Tushar Mehta (Appendix A: "Code to retrieve trendline coefficients")