Trendline Coefficients

[johnhildreth]

I have a function that reads the coefficients of a trendline by
searching the datalabel.text It is by no means sophisticated, but it
works well. It is designed only for second order polynomials and
returns the a, b, or c coefficient (y=ax^2+bx+c) based on the user
"reference". That is =coeff("a") returns the a coefficient.

My question is....since the function doesn't truly reference anything
but the code, it doesn't update when the underlying data changes.
When the data changes the trendline is updated, but nothing triggers
the function to "recalculate". Is there a way to reference the chart,
such that when it changes the value is recalculated?.....will this
even work like changing the value in a referenced cell??

Thanks,
John

Public Function coeff(var As String) As Double

sEqn = Charts(1).SeriesCollection(1).Trendlines(1).DataLabel.Text

fst = InStr(1, sEqn, "x2")
snd = InStr(fst + 1, sEqn, "x")

If Mid(sEqn, fst + 3, 1) = "+" Then
bsign = True
Else
bsign = False
End If

If Mid(sEqn, snd + 2, 1) = "+" Then
csign = True
Else
csign = False
End If

If var = "a" Then
coeff = Mid(sEqn, 5, fst - 5)
ElseIf var = "b" Then

If bsign = True Then
coeff = Mid(sEqn, fst + 5, snd - (fst + 5))
Else
coeff = "-" & Mid(sEqn, fst + 5, snd - (fst + 5))
End If

ElseIf var = "c" Then

If csign = True Then
coeff = Mid(sEqn, snd + 4, Len(sEqn) - snd + 3)
Else
coeff = "-" & Mid(sEqn, snd + 4, Len(sEqn) - snd + 3)
End If

End If

End Function

 

[Tushar Mehta]

You have to make the function "volatile" For more search XL VBA help
for that term.

You may also want to look at code first shared by David Braden and
subsequently modified by me. See
Trendline Coefficients and Regression Analysis
http://www.tushar-mehta.com/publish_train/data_analysis/16.htm

 

[johnhildreth]

Thanks. I made the function "volatile" and now it works with the F9
key (Reclaculate), but still doesn't update on its own. I can
certainly do the regression from first principles using XL. But since
I want to see the data and trendline graphically anyway, I might as
well use the equation.

John

 

[Mike Middleton]

John -

Some ideas: A well-behaved user defined function will recalculate when the
arguments change. Instead of "first principles," you could use LINEST for
the calculations and OFFSET to get the individual coefficients, with or
without VBA. If you really want a VBA function, it could refer to the
worksheet data as its arguments, and then it would recalculate when the data
changed.

- Mike
www.MikeMiddleton.com