Remember Me?

 Miguel Saldana Posts: n/a Excel option to store trendline's coefficients in cells for use

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 Posts: n/a 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 Posts: n/a Alternately, David Braden has posted VBA code to extract the
coefficients directly from the chart into cells

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 Posts: n/a Hi Jerry,

Can you post that link again, for some reason it's truncated and returns

Cheers
Andy

Jerry W. Lewis wrote:
Alternately, David Braden has posted VBA code to extract the
coefficients directly from the chart into cells

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 Posts: n/a 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

Cheers
Andy

Jerry W. Lewis wrote:

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

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 Posts: n/a Thanks Jerry.

Jerry W. Lewis wrote:

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

Cheers
Andy

Jerry W. Lewis wrote:

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

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 Posts: n/a Any time.

Andy Pope wrote:

Thanks Jerry.

 Tushar Mehta Posts: n/a 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
+ Technology skills
Recipient Microsoft MVP award 2000-2005

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

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 Posts: n/a 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 Posts: n/a Hi Jerry,

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)
'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 ,
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.

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Randy Excel Discussion (Misc queries) 9 June 14th 05 10:27 PM Trendy Charts and Charting in Excel 3 May 20th 05 07:03 AM Karl Schweitzer Excel Discussion (Misc queries) 0 April 7th 05 06:17 PM Margaret Excel Discussion (Misc queries) 1 April 7th 05 04:20 PM BeefmanSteve Charts and Charting in Excel 2 December 22nd 04 10:53 PM

All times are GMT +1. The time now is 09:43 PM. Copyright ©2004-2021 ExcelBanter.