Home 
Search 
Today's Posts 
#1




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 Webbased Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...excel.charting 
#2




Hi,
You can get those values via formula, see this explanations. http://www.stfx.ca/people/bliengme/E...Polynomial.htm http://tusharmehta.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 Webbased 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




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://tusharmehta.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




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://tusharmehta.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




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




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




Any time.
Andy Pope wrote: Thanks Jerry. 
#8




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.tusharmehta.com Multidisciplinary business expertise + Technology skills = Optimal solution to your business problem Recipient Microsoft MVP award 20002005 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://tusharmehta.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




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 leastsquares 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




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 shiftclick it; its name will appear in the _ dropdown 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.tusharmehta.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. (Rightclick _ 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 higherorder 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.tusharmehta.com Excel, PowerPoint, and VBA addins, 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 leastsquares 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 
Display Modes  


Similar Threads  
Thread  Forum  
Excel startup switches  Excel Discussion (Misc queries)  
Does Excel use least squares regression to calculate trendlines?  Charts and Charting in Excel  
Excel instance used with "Export to Microsoft Excel" option in Internet Explorer  Excel Discussion (Misc queries)  
Excel sort option disabledfile name followed by "group" at top .  Excel Discussion (Misc queries)  
How does Excel 2003 calculate trendlines with respect to axes?  Charts and Charting in Excel 