Use a Trendline Formula to Get Values for Any Given X with Excel

Question

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.

Answer 1

You can write a vba user defined function to use the trend line formula to evaluate a given x
Here's an example to get started

Function TrendLineValue(x As Double) As Double
    Dim c As Chart
    Dim t As Trendline
    Dim s As String

    ' Get the trend line object
    ' this code assumes the first chart on the active sheet, 
    '   and the first series, first trendline
    Set c = ActiveSheet.ChartObjects(1).Chart
    Set t = c.SeriesCollection(1).Trendlines(1)

    ' make sure equation is displayed
    t.DisplayRSquared = False
    t.DisplayEquation = True

    ' set number format to ensure accuracy
    '   adjust to suit requirements
    t.DataLabel.NumberFormat = "0.0000E+00"

    ' get the equation
    s = t.DataLabel.Text

    ' massage the equation string into form that will evaluate
    ' this code assumes 3rd order polynomial
    s = Replace(s, "y =", "")
    s = Replace(s, "x3", "x^3")
    s = Replace(s, "x2", "x^2")
    s = Replace(s, "x", " * " & x & " ")

    ' evaluate for given x value
    TrendLineValue = Evaluate(s)
End Function

Answer 2

You can solve this with a simple LINEST formula (without charting)

For a 3rd degree array enter =LINEST(C2:C15,B2:B15^{1,2,3}) into four horizontal cells to get your equation coefficients (aX^3+bX^2+cX+D), and then just substitute for X

LINEST for more complex regressions covered at http://office.microsoft.com/en-us/excel-help/linest-HP005209155.aspx