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

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. ## 3 Answers

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
``````
• Thank you for this, it's great though a little easier could be to use `s = Replace(s, "x", "x^")` :) – Buksy Apr 13 '13 at 20:15
• @Busky but that would catch the `x` term too. – chris neilsen Apr 13 '13 at 20:55

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 is covered here

• I don't want to vote down the trendline formula vba answer, but I want to say that LINEST is much easier than the VBA approach, because it uses calculations directly, not a formula which may not be formatted tfor sufficient accuracy (see W_Whalley's earlier comment: use a number format of 0.000000000000E+00 to help improve the trendline formula's accuracy). – Jon Peltier Nov 27 '12 at 21:40

I found a solution that works for every type of trendlines (exept for moving average of course). You may want to set the precision of the Datalabel to suit your needs.

``````Option Explicit

'Testdrive for the function
Public Sub main()
Dim sht As Worksheet
Dim graph As ChartObject
Dim formula As String
Dim x As Double
Dim result As String

Set sht = Sheets("graph")

'I have a sheet with one scatter plot in sheet "graph"
Set graph = sht.ChartObjects(1)

'Set the x value to evaluate at
x = 56

result = calcTrendlineValueForX(graph.Chart.SeriesCollection(1).Trendlines(1), x)

Debug.Print "f(" & x & ") = " & result
End Sub

'
' Evaluate a trendline at a certain x
' Param      : * {trendline object} The trendline to use
'              * {double} the x value
' Return     : * {double} The value for a given x
'
Public Function calcTrendlineValueForX(trendline As trendline, xValue As Double) As Double
Dim trendlineWasVisible As Boolean
Dim i As Integer
Dim char As String
Dim preChar As String
Dim newFormula As String
Dim bCharIsPower As Boolean
Dim bPreCharIsPower As Boolean

'If the trendline is a moving average, return 0
If trendline.Type = xlMovingAvg Then
newFormula = "0"
Else
'If equation is logarithmic and x <= 0, return 0
If trendline.Type = xlLogarithmic And xValue <= 0 Then
newFormula = "0"
Else
'Keep track of the style of the trendline.
'You may set the precision here
trendlineWasVisible = trendline.DisplayEquation

'Display the equation of the trenline
If Not trendlineWasVisible Then
trendline.DisplayEquation = True
End If

newFormula = ""
bPreCharIsPower = False
bCharIsPower = False

preChar = ""

'Loop equation char by char
For i = 1 To trendline.DataLabel.Characters.Count
char = Mid(trendline.DataLabel.Characters.Text, i, 1)   'get the actual char

'Look if the char in written in superscript
bCharIsPower = trendline.DataLabel.Characters(i).Font.Superscript

'Treat the superscript
If bCharIsPower And Not bPreCharIsPower Then
newFormula = newFormula & "^("

Else
If Not bCharIsPower And bPreCharIsPower Then
newFormula = newFormula & ")"
preChar = ")"
End If
End If

'if actual char is "x" or "e"
If char = "x" Or char = "e" Then
'If we need to add a "*" before the actual char
If preChar = "x" Or preChar = "e" Or preChar = ")" Or IsNumeric(preChar) Then
newFormula = newFormula & " * " & char
Else
'Add the char to the new formula string
newFormula = newFormula & char
End If
Else
'if "ln"
If char = "l" Then
'If we need to add a "*" before the "ln"
If preChar = "x" Or preChar = "e" Or IsNumeric(preChar) Or preChar = ")" Then
newFormula = newFormula & " * l"
Else
'Add the char to the new formula string
newFormula = newFormula & char
End If

Else
'Process for numeric
If IsNumeric(char) Then
If preChar = ")" Then
newFormula = newFormula & "*" & char
Else
'Add the char to the new formula string
newFormula = newFormula & char
End If
Else
'Add the char to the new formula string
newFormula = newFormula & char
End If
End If

End If

'Keep track of the preceding char
preChar = char
bPreCharIsPower = bCharIsPower
Next i

'Add parenthesis if the formula finishes with a superscript char
If bCharIsPower Then
newFormula = newFormula & ")"
End If

'Put back the trendline equation like it was before
'If you have set the precision, you can set it back here
trendline.DisplayEquation = trendlineWasVisible

'Format the new formula to be understanding by Evaluate() function
newFormula = Replace(newFormula, "y =", "")  'Strips "y ="
newFormula = Replace(newFormula, Application.DecimalSeparator, ".") 'Replace decimal separator
newFormula = Replace(newFormula, "x", xValue)  'Assign the given x
newFormula = Replace(newFormula, "e^", "exp")  'e
newFormula = Replace(newFormula, " ", "")  'Strip spaces (occurs on the formating of some sort)
End If

End If

calcTrendlineValueForX = Evaluate(newFormula)
End Function
``````