Extracting trend line function

Question

I would like to know if it is possible to use the trend lines function of graphs in a cell.

This way if the graph database changes, and consequently the function, I can refer to an updated function.

Anyone who think it would be possibile?

Thanks in advance,

Luigi

Answer 1

Hi

For a series of values in a row, we can insert a sparkline.

The reference: Analyze trends in data using sparklines

But we cannot insert the trendline of a chart into a cell. Thanks for your understanding.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

Hi Luigi,

not really. We can use VBA to get a trendline forumla and transform it to an Excel formula, see code below.

The issue is that the trendline fomrula in a chart updates AFTER the calculation...

Example:

Let us assume we use this formula to generate values for a chart:

A2: =RANDBETWEEN(1,9)

drag down.

We can build e.g. a line chart from that values, add a trendline.

Now we use the function below to get the formula from the trendline.

So far no issue. But if we try to write or update an existing formula in the file forces a recalculation.

And so the values in column A are also recalculated, which causes the chart to recalculate, which causes the trendline to be updated... endless loop.

Andreas.

Function TrendLineFormula(ByVal RefCellForX As Range, ByVal ChartObjectOrName, Optional SeriesIndexOrName, Optional TrendIndexOrName) As Variant
  'Returns a trendline formula from a chart that can be used in a cell
  Dim CO As ChartObject
  Dim C As Chart
  Dim S As Series
  Dim T As Trendline
  Dim F As String
  Dim i As Integer
  
  On Error Resume Next
  If VarType(ChartObjectOrName) = vbObject Then
    Set C = ChartObjectOrName
  Else
    Set CO = ActiveSheet.ChartObjects(ChartObjectOrName)
    If CO Is Nothing Then
      TrendLineFormula = CVErr(xlErrName)
      Exit Function
    End If
    Set C = CO.Chart
  End If
  If IsMissing(SeriesIndexOrName) Then
    Set S = C.SeriesCollection(1)
  Else
    Set S = C.SeriesCollection(SeriesIndexOrName)
  End If
  If S Is Nothing Then
    TrendLineFormula = CVErr(xlErrName)
    Exit Function
  End If
  If S.Trendlines.Count = 0 Then
    TrendLineFormula = CVErr(xlErrNull)
    Exit Function
  End If
  If IsMissing(TrendIndexOrName) Then
    Set T = S.Trendlines(1)
  Else
    If VarType(TrendIndexOrName) = vbString Then
      For Each T In S.Trendlines
        Select Case T.Type
          Case xlExponential
            If InStr(1, TrendIndexOrName, "Ex", vbTextCompare) = 1 Then Exit For
          Case xlLinear
            If InStr(1, TrendIndexOrName, "Li", vbTextCompare) = 1 Then Exit For
          Case xlLogarithmic
            If InStr(1, TrendIndexOrName, "Lo", vbTextCompare) = 1 Then Exit For
          Case xlPolynomial
            If InStr(1, TrendIndexOrName, "Pol", vbTextCompare) = 1 Then Exit For
          Case xlPower
            If InStr(1, TrendIndexOrName, "Pow", vbTextCompare) = 1 Then Exit For
        End Select
      Next
    Else
      Set T = S.Trendlines(TrendIndexOrName)
    End If
  End If
  If S Is Nothing Then
    TrendLineFormula = CVErr(xlErrField)
    Exit Function
  End If
  If Not T.DisplayEquation Then
    TrendLineFormula = CVErr(xlErrCalc)
    Exit Function
  End If
  F = Split(T.DataLabel.Formula, vbVerticalTab)(0)
  F = Mid(F, InStr(F, "="))
  For i = 1 To 9
    F = Replace(F, "x" & i, "x^" & i)
  Next
  TrendLineFormula = Replace(F, "x", "*" & RefCellForX.Address(0, 0))
End Function