• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1275
  • Last Modified:

Excel: retrieving a trendline equation by code

I have a simple graph, 2 dimensions x and y, y=f(x) but the equation/relation is unknown and I want it !
So I insert a trendline (polynomial level 6 to have the best match). Then I display the trendline equation on the graph.
If I test the equation, applying to the x value the equation, the output is completly out of range.. I tried several times, always wrong output. Either I do not know how to read the equation or it is wrong or incorrectly displayed.
Can I retrieve that equation by any other means such as a bit of code in Visual Basic ?
thanks
0
lilletof
Asked:
lilletof
  • 4
  • 2
1 Solution
 
criCommented:
VBA is a bit too much for testing, but it can be done. But first highlight the equation and copy it to clipboard. Paste it into a cell and make a formula out of it by removing the y and replacing the x with the cell. This way you prevent a typo.
0
 
criCommented:
As for VBA, will look it up, be back soon.
0
 
criCommented:
Code courtesy of VBoukhAr in question Q.10188253:

This one gets it as string only:

Sub GetTrend()
 Dim strLabel As String
 strLabel = ThisWorkbook.Charts(1).SeriesCollection(1).Trendlines(1).DataLabel.Text
 MsgBox strLabel
End Sub

This parses the retrieved string. It assumes that you have Chart on _separate_ sheet. Open VBA project (Alt+F11) and copy-paste code below. Then run it (F5). In last rows at first worksheet you'll see full equation and separate coefficients of X in correspondent power.

Sub EquProc()
  Dim tmpStr As String, tmpNum As String, tmpX As String
  Dim Power As Boolean, k As Integer, LastRow As Integer
  tmpStr = ThisWorkbook.Charts(1).SeriesCollection(1).Trendlines(1).DataLabel.Text
  LastRow = Worksheets(1).UsedRange.Row + Worksheets(1).UsedRange.Rows.Count
  Worksheets(1).Cells(LastRow + 1, 1) = tmpStr
  k = 1
  Power = False
  tmpNum = "" 
  Do Until Len(tmpStr) <= 0
    Select Case Left(tmpStr, 1)
      Case "0" To "9", ",", "-", "+", "E"
         If Not Power Then
           tmpNum = tmpNum & Left(tmpStr, 1)
         Else
           tmpX = tmpX & Left(tmpStr, 1)
         End If
      Case "x", "X"
         Power = True
         tmpX = "x"
      Case Else
         If Power Then
           Worksheets(1).Cells(LastRow + 2, k) = tmpX
           Worksheets(1).Cells(LastRow + 3, k) = CDbl(tmpNum)
           tmpNum = "" 
           tmpX = "const"
           Power = False
           k = k + 1
         End If
      End Select
      tmpStr = Right(tmpStr, Len(tmpStr) - 1)
    Loop
    Worksheets(1).Cells(LastRow + 2, k) = tmpX
    Worksheets(1).Cells(LastRow + 3, k) = CDbl(tmpNum)
End Sub
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
tureCommented:
lilletof,

The cause of this is that the trendline equation is displayed with too low precision. Do this:

1. Create a chart and add a trendline (you know how to do that)
2. Right-click the trendline equation and do 'Format Datalabels'
3. On the 'Number' tab, choose category 'Number' and increase the decimal places to 8 or something.

Now you can use the values displayed in the trendline equation. It should work a lot better.

Ture Magnusson
Karlstad, Sweden
0
 
criCommented:
ture, this _could_ be the cause, but as liletof wrote completely out of range s/he must coefficients with only 1-2 significant digits. In my opinion a typo or wrong sign is more likely.  
0
 
tureCommented:
Cri,
Perhaps you are right, perhaps not... I've faced this particular problem before and in that case it was a precision problem.
/Ture
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now