asked on

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

ASKER CERTIFIED SOLUTION

cri

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cri

As for VBA, will look it up, be back soon.

cri

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

ture

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

cri

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.

ture

Cri,
Perhaps you are right, perhaps not... I've faced this particular problem before and in that case it was a precision problem.
/Ture