Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 14th 05, 04:16 PM
Miguel Saldana
 
Posts: n/a
Default 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

  #2   Report Post  
Old June 14th 05, 05:50 PM
Andy Pope
 
Posts: n/a
Default

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
  #3   Report Post  
Old June 15th 05, 01:01 PM
Jerry W. Lewis
 
Posts: n/a
Default

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.


  #4   Report Post  
Old June 15th 05, 01:38 PM
Andy Pope
 
Posts: n/a
Default

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
  #5   Report Post  
Old June 16th 05, 02:24 PM
Jerry W. Lewis
 
Posts: n/a
Default

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




  #6   Report Post  
Old June 16th 05, 05:24 PM
Andy Pope
 
Posts: n/a
Default

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
  #7   Report Post  
Old June 17th 05, 02:54 AM
Jerry W. Lewis
 
Posts: n/a
Default

Any time.

Andy Pope wrote:

Thanks Jerry.


  #8   Report Post  
Old June 17th 05, 05:59 PM
Tushar Mehta
 
Posts: n/a
Default

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



  #9   Report Post  
Old June 17th 05, 10:59 PM
Jerry W. Lewis
 
Posts: n/a
Default

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.


  #10   Report Post  
Old June 20th 05, 08:45 PM
Tushar Mehta
 
Posts: n/a
Default

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel startup switches Randy Excel Discussion (Misc queries) 9 June 14th 05 10:27 PM
Does Excel use least squares regression to calculate trendlines? Trendy Charts and Charting in Excel 3 May 20th 05 07:03 AM
Excel instance used with "Export to Microsoft Excel" option in Internet Explorer Karl Schweitzer Excel Discussion (Misc queries) 0 April 7th 05 06:17 PM
Excel sort option disabled--file name followed by "group" at top . Margaret Excel Discussion (Misc queries) 1 April 7th 05 04:20 PM
How does Excel 2003 calculate trendlines with respect to axes? 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.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017