Excel option to store trendline's coefficients in cells for use

[Miguel Saldana]

I believe it will be useful to add an option to Excel that when adding a
tendline to a graph, Excel could ask in which cells it will store the
coefficients and exponents in the resulting trendline. In this way, the user
can use those coefficients in other calculations.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...excel.charting

[Andy Pope]

Hi,

You can get those values via formula, see this explanations.

http://www.stfx.ca/people/bliengme/E...Polynomial.htm
http://tushar-mehta.com/excel/tips/t...efficients.htm

Cheers
Andy

Miguel Saldana wrote:
> I believe it will be useful to add an option to Excel that when adding a
> tendline to a graph, Excel could ask in which cells it will store the
> coefficients and exponents in the resulting trendline. In this way, the user
> can use those coefficients in other calculations.
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...excel.charting

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[Jerry W. Lewis]

Alternately, David Braden has posted VBA code to extract the
coefficients directly from the chart into cells

http://groups.google.com/groups?selm...54705032003%40...

The advantage of using the chart coefficients (either manually or via
Braden's code) is that LINEST (prior to Excel 2003) uses a numerically
poor algorithm that can give inaccurate results with some data sets.
The chart trendline (extracted by Braden's code) is much better numerically.

Jerry

Andy Pope wrote:

> Hi,
>
> You can get those values via formula, see this explanations.
>
> http://www.stfx.ca/people/bliengme/E...Polynomial.htm
> http://tushar-mehta.com/excel/tips/t...efficients.htm
>
> Cheers
> Andy
>
> Miguel Saldana wrote:
>
>> I believe it will be useful to add an option to Excel that when adding
>> a tendline to a graph, Excel could ask in which cells it will store
>> the coefficients and exponents in the resulting trendline. In this
>> way, the user can use those coefficients in other calculations.

[Andy Pope]

Hi Jerry,

Can you post that link again, for some reason it's truncated and returns
a Not found @ google.

Cheers
Andy

Jerry W. Lewis wrote:
> Alternately, David Braden has posted VBA code to extract the
> coefficients directly from the chart into cells
>
> http://groups.google.com/groups?selm...54705032003%40...
>
> The advantage of using the chart coefficients (either manually or via
> Braden's code) is that LINEST (prior to Excel 2003) uses a numerically
> poor algorithm that can give inaccurate results with some data sets. The
> chart trendline (extracted by Braden's code) is much better numerically.
>
> Jerry
>
> Andy Pope wrote:
>
>> Hi,
>>
>> You can get those values via formula, see this explanations.
>>
>> http://www.stfx.ca/people/bliengme/E...Polynomial.htm
>> http://tushar-mehta.com/excel/tips/t...efficients.htm
>>
>> Cheers
>> Andy
>>
>> Miguel Saldana wrote:
>>
>>> I believe it will be useful to add an option to Excel that when
>>> adding a tendline to a graph, Excel could ask in which cells it will
>>> store the coefficients and exponents in the resulting trendline. In
>>> this way, the user can use those coefficients in other calculations.
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[Jerry W. Lewis]

http://groups.google.com/groups?selm....microsoft.com

If you still have problems, the Google link from my post in the
"Trendline equation in chart is wrong" thread (Nov 2003) should work.

Jerry

Andy Pope wrote:

> Hi Jerry,
>
> Can you post that link again, for some reason it's truncated and returns
> a Not found @ google.
>
> Cheers
> Andy
>
> Jerry W. Lewis wrote:
>
>> Alternately, David Braden has posted VBA code to extract the
>> coefficients directly from the chart into cells
>>
>> http://groups.google.com/groups?selm...54705032003%40...
>>
>> The advantage of using the chart coefficients (either manually or via
>> Braden's code) is that LINEST (prior to Excel 2003) uses a numerically
>> poor algorithm that can give inaccurate results with some data sets.
>> The chart trendline (extracted by Braden's code) is much better
>> numerically.
>>
>> Jerry

[Andy Pope]

Thanks Jerry.

Jerry W. Lewis wrote:
> http://groups.google.com/groups?selm....microsoft.com
>
>
> If you still have problems, the Google link from my post in the
> "Trendline equation in chart is wrong" thread (Nov 2003) should work.
>
> Jerry
>
> Andy Pope wrote:
>
>> Hi Jerry,
>>
>> Can you post that link again, for some reason it's truncated and
>> returns a Not found @ google.
>>
>> Cheers
>> Andy
>>
>> Jerry W. Lewis wrote:
>>
>>> Alternately, David Braden has posted VBA code to extract the
>>> coefficients directly from the chart into cells
>>>
>>> http://groups.google.com/groups?selm...54705032003%40...
>>>
>>> The advantage of using the chart coefficients (either manually or via
>>> Braden's code) is that LINEST (prior to Excel 2003) uses a
>>> numerically poor algorithm that can give inaccurate results with some
>>> data sets. The chart trendline (extracted by Braden's code) is much
>>> better numerically.
>>>
>>> Jerry
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[Jerry W. Lewis]

Any time.

Andy Pope wrote:

> Thanks Jerry.

[Tushar Mehta]

Hi Jerry,

Do be aware that while Dave Braden's code might be the best option for
the job at hand, it has limitations. If I remember correctly, one of
them is that if a coefficient is 1, XL doesn't show it and the code
fails to handle that correctly. I have an improved version on some
computer but haven't shared it with the world because it has been tested
very lightly and, of course, XL2003 came along.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article <42B018A1.50702@no_e-mail.com>, post_a_reply@no_e-mail.com
says...
> Alternately, David Braden has posted VBA code to extract the
> coefficients directly from the chart into cells
>
> http://groups.google.com/groups?selm...54705032003%40...
>
> The advantage of using the chart coefficients (either manually or via
> Braden's code) is that LINEST (prior to Excel 2003) uses a numerically
> poor algorithm that can give inaccurate results with some data sets.
> The chart trendline (extracted by Braden's code) is much better numerically.
>
> Jerry
>
> Andy Pope wrote:
>
> > Hi,
> >
> > You can get those values via formula, see this explanations.
> >
> > http://www.stfx.ca/people/bliengme/E...Polynomial.htm
> > http://tushar-mehta.com/excel/tips/t...efficients.htm
> >
> > Cheers
> > Andy
> >
> > Miguel Saldana wrote:
> >
> >> I believe it will be useful to add an option to Excel that when adding
> >> a tendline to a graph, Excel could ask in which cells it will store
> >> the coefficients and exponents in the resulting trendline. In this
> >> way, the user can use those coefficients in other calculations.
>
>

[Jerry W. Lewis]

As I recall, Braden's code also required that the graph be on a
worksheet, and would only extract the displayed precision.
Unfortunately, the chart trendline remains Excel's most stable and
accurate least-squares platform (hopefully that will change with the
next version of Excel). I would be glad to put your code through its
paces if you so desire.

Jerry

Tushar Mehta wrote:

> Hi Jerry,
>
> Do be aware that while Dave Braden's code might be the best option for
> the job at hand, it has limitations. If I remember correctly, one of
> them is that if a coefficient is 1, XL doesn't show it and the code
> fails to handle that correctly. I have an improved version on some
> computer but haven't shared it with the world because it has been tested
> very lightly and, of course, XL2003 came along.

[Tushar Mehta]

Hi Jerry,

Here it is. Look forward to your comments.

Option Explicit
Option Base 0
'Function TLcoef(...) returns Trendline coefficients
'Function TLeval(x, ...) evaluates the current trendline at a given x
'
'The arguments of TLcoef, and the last 4 of TLeval: _
vSheet is the name/number of the sheet containing the chart. _
Use of the name (as in the Sheet's tab) is recommended _
vCht is the name/number of the chart. To see this, deselect _
the chart, then shift-click it; its name will appear in the _
drop-down list at the left of formula bar. In the case of a _
chart in its own chartsheet, specify this as zero or the zero _
length string "" _
VSeries is a series name/number, and vTL is the series' trendline _
number. If the series has a name, it is probably better to _
specify the name. To determine the name/number, as well as _
the trendline number needed for vTL, pass the mouse arrow _
over the trendline. Of course, if there is only one series in _
the chart, you can set vSeries = 1, but beware if you add _
more series to the chart.

'First draft written 2003 March 1 by D J Braden _
Revisions by Tushar Mehta (www.tushar-mehta.com) 2005 Jun 19: _
Various documentation changes _
vCht is now 'optional' _
Correctly handles cases where a term is missing -- e.g., _
y = 2x3 + 3x + 10 _
Correctly handles cases where a coefficient is not shown because _
it is the default value -- e.g., y = Ln(x)+10 _
When only the constant term is present, the original function _
returned it in the correct array element only for the _
polynomial and linear fits. Now, the function returns it in _
the correct array element for other types also. For example, _
for an exponential fit, y=10 will be returned as (10,0) _
Arrays are now base zero.
'Limitations: _
The coefficients are returned to precision *displayed* _
To get the most accurate values, format the trendline label _
to scientific notation with 14 decimal places. (Right-click _
the label to do this) _
Given how XL calculation engine works -- recalculates the _
worksheet first, then the chart(s) -- it is eminently _
possible for the chart to show one trendline and the _
function to return coefficients corresponding to the values _
shown by the chart *prior* to the recalculation. To see the _
effect of this '1 recalculation cycle lag' plot a series of _
random numbers. _
An alternative to the functions in this module is the LINEST _
worksheet function. Except for those few cases where LINEST _
returns incorrect results, it is the more robust function _
since it doesn't suffer from the '1 recalculation cycle' _
lag. With XL2003 LINEST may even return more accurate _
results than the trendline.

Function TLcoef(vSheet, vCht, vSeries, vTL)
'To get the coefficients of a chart on a chartsheet, specify vCht _
as zero or the zero length string ""

'Return coefficients of an Excel chart trendline. _
Limitations: See the documentation at the top of the module _
'Note: For a polynomial fit, it is possible the trendline doesn't _
report all the terms. So this function returns an array of _
length (1 + the order of the requested fit), *not* the number of _
values displayed. The last value in the returned array is the _
constant term; preceeding values correspond to higher-order x.
Dim o As Trendline
Application.Volatile
If ParamErr(TLcoef, vSheet, vCht, vSeries, vTL) Then Exit Function
On Error Resume Next
If vCht = "" Or vCht = 0 Then
If TypeOf Sheets(vSheet) Is Chart Then
Set o = Sheets(vSheet).SeriesCollection(vSeries) _
.Trendlines(vTL)
Else
TLcoef = "#Err: vCht can be omitted only if vSheet is a " _
& "chartsheet"
Exit Function '*****
End If
Else
Set o = Sheets(vSheet).ChartObjects(vCht).Chart. _
SeriesCollection(vSeries).Trendlines(vTL)
End If
On Error GoTo 0
If o Is Nothing Then
TLcoef = "#Err: No trendline matches the specified parameters"
Else
TLcoef = ExtractCoef(o)
End If
End Function


Function TLeval(vX, vSheet, vCht, vSeries, vTL)
'DJ Braden
'Exp/logs are done for cases xlPower and xlExponential to _
allow for greater range of arguments.
Dim o As Trendline, vRet

Application.Volatile
If ParamErr(TLeval, vSheet, vCht, vSeries, vTL) Then Exit Function
On Error Resume Next
If vCht = "" Or vCht = 0 Then
If TypeOf Sheets(vSheet) Is Chart Then
Set o = Sheets(vSheet).SeriesCollection(vSeries) _
.Trendlines(vTL)
Else
TLeval = "#Err: vCht can be omitted only if vSheet is a " _
& "chartsheet"
Exit Function '*****
End If
Else
Set o = Sheets(vSheet).ChartObjects(vCht).Chart. _
SeriesCollection(vSeries).Trendlines(vTL)
End If
On Error GoTo 0
If o Is Nothing Then
TLeval = "#Err: No trendline matches the specified parameters"
Exit Function
End If
vRet = ExtractCoef(o)
If TypeName(vRet) = "String" Then TLeval = vRet: Exit Function
Select Case o.Type
Case xlLinear
TLeval = vX * vRet(LBound(vRet)) + vRet(UBound(vRet))
Case xlExponential 'see comment above
TLeval = Exp(Log(vRet(LBound(vRet))) + vX * vRet(UBound(vRet)))
Case xlLogarithmic
TLeval = vRet(LBound(vRet)) * Log(vX) + vRet(UBound(vRet))
Case xlPower 'see comment above
TLeval = Exp(Log(vRet(LBound(vRet))) _
+ Log(vX) * vRet(UBound(vRet)))
Case xlPolynomial
Dim Idx As Long
TLeval = vRet(LBound(vRet)) * vX + vRet(LBound(vRet) + 1)
For Idx = LBound(vRet) + 2 To UBound(vRet)
TLeval = vX * TLeval + vRet(Idx)
Next Idx
End Select
End Function

Private Function DecodeOneTerm(ByVal TLText As String, _
ByVal SearchToken As String, _
ByVal UnspecifiedConstant As Byte)
'splits {optional number}{SearchToken} _
{optional numeric constant}
Dim v(1) As Double, TokenLoc As Long
TokenLoc = InStr(1, TLText, SearchToken, vbTextCompare)
If TokenLoc = 0 Then
v(1) = CDbl(TLText)
Else
If TokenLoc = 1 Then v(0) = 1 _
Else v(0) = Left(TLText, TokenLoc - 1)
If TokenLoc + Len(SearchToken) > Len(TLText) Then _
v(1) = UnspecifiedConstant _
Else v(1) = Mid(TLText, TokenLoc + Len(SearchToken))
End If
DecodeOneTerm = v
End Function
Private Function getXPower(ByVal TLText As String, _
ByVal XPos As Long)
If XPos = Len(TLText) Then
getXPower = 1
ElseIf IsNumeric(Mid(TLText, XPos + 1, 1)) Then
getXPower = Mid(TLText, XPos + 1, 1)
Else
getXPower = 1
End If
End Function

Private Function ExtractCoef(o As Trendline)
Dim XPos As Long, s As String
On Error Resume Next
s = o.DataLabel.Text
On Error GoTo 0
If s = "" Then
ExtractCoef = "#Err: No trendline equation found"
Exit Function '*****
End If
If o.DisplayRSquared Then s = Left$(s, InStr(s, "R") - 2)
s = Trim(Mid(s, InStr(1, s, "=", vbTextCompare) + 1))
Select Case o.Type
Case xlMovingAvg
Case xlLogarithmic
ExtractCoef = DecodeOneTerm(s, "Ln(x)", 0)
Case xlLinear
ExtractCoef = DecodeOneTerm(s, "x", 0)
Case xlExponential
s = Application.WorksheetFunction.Substitute(s, "x", "")
ExtractCoef = DecodeOneTerm(s, "e", 1)
Case xlPower
ExtractCoef = DecodeOneTerm(s, "x", 1)
Case xlPolynomial
Dim lOrd As Long
ReDim v(o.Order) As Double
s = Application.WorksheetFunction.Substitute(s, " ", "")
s = Application.WorksheetFunction.Substitute(s, "+x", "+1x")
s = Application.WorksheetFunction.Substitute(s, "-x", "-1x")
Do While s <> ""
XPos = InStr(1, s, "x")
If XPos = 0 Then
v(o.Order) = s 'constant term
s = ""
Else
lOrd = getXPower(s, XPos)
If XPos = 1 Then v(UBound(v) - lOrd) = 1 _
Else _
v(UBound(v) - lOrd) = Left(s, XPos - 1)
If XPos = Len(s) Then
s = ""
ElseIf IsNumeric(Mid(s, XPos + 1, 1)) Then
s = Trim(Mid(s, XPos + 2))
Else
s = Trim(Mid(s, XPos + 1))
End If
End If
Loop
ExtractCoef = v
End Select
End Function
Private Function ParamErr(v, ParamArray parms())
Dim l As Long
For l = LBound(parms) To UBound(parms)
If VarType(parms(l)) = vbError Then
v = parms(l)
ParamErr = True
Exit Function
End If
Next l
End Function

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <42B347C6.1060403@no_e-mail.com>, post_a_reply@no_e-mail.com
says...
> As I recall, Braden's code also required that the graph be on a
> worksheet, and would only extract the displayed precision.
> Unfortunately, the chart trendline remains Excel's most stable and
> accurate least-squares platform (hopefully that will change with the
> next version of Excel). I would be glad to put your code through its
> paces if you so desire.
>
> Jerry
>
> Tushar Mehta wrote:
>
> > Hi Jerry,
> >
> > Do be aware that while Dave Braden's code might be the best option for
> > the job at hand, it has limitations. If I remember correctly, one of
> > them is that if a coefficient is 1, XL doesn't show it and the code
> > fails to handle that correctly. I have an improved version on some
> > computer but haven't shared it with the world because it has been tested
> > very lightly and, of course, XL2003 came along.
>
>