# Trendline Formula – Use a Trendline Formula to Get Values for Any Given X in Excel

excelregression

Is there an easy way to apply the trend line formula from a chart to any given X value in Excel?

For example, I want to get the Y value for a given X = \$2,006.00. I've already taken the formula and retyped it out be:

=-0.000000000008*X^3 - 0.00000001*X^2 + 0.0003*X - 0.0029


I am continually making adjustments to the trend line by adding more data, and don't want to retype out the formula every time.

Use LINEST, as shown:
The method is to create new columns (C:E here) containing the variables in the fit. Perversely, LINEST returns the coefficients in the reverse order, as you can see from the calculation in the "Fit" column. An example prediction is shown outlined in blue: all the formulas are exactly the same as for the data.
Note that LINEST is an array formula: the answer will occupy p+1 cells in a row, where p is the number of variables (the last one is for the constant term). Such formulas are created by selecting all the output cells, pasting (or typing) the formula in the formula textbox, and pressing Ctrl-Shift-Enter (instead of the usual Enter).