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. 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
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^") :) Apr 13 '13 at 20:15
• @Busky but that would catch the x term too. 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). Nov 27 '12 at 21:40
• LINEST for more complex regressions is covered here is now a dead link. A quick search didn't reveal they have moved the page to. Jul 2 '20 at 1:36

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