Extract formula from Text box

[CLR]

Hi All.........

If someone would please be so kind..........I am in need of code to extract
the formula from the Text Box that is put there when one creates an XY
Scatter chart and adds a Third-order Polynomial Trendline. I am trying to
create a macro that will do this automatically and then do normal
Text-to-columns and break it apart and then do the math.........I can get it
all working by recording the macro, but for some reason I can't "get" the
formula out of the text box, only by Cntrl-C have I been able to copy and
paste it to a cell, and that doesn't "record" too well.........I know about
the LINEST function, but for some reason it's answer does not jive with that
from the formula from the text box...........my user wants to use the
formula from the text box.

Here's the code I get by recording, it seems to work for a time or two, then
not.....and if I clear the trendline and change a value and make another
trendline, the macro does not copy out the same formula thats in the text
box........nor will it change when I increase precision of the
formula.........?

Sub GetTrendlineFormula()
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
ActiveWindow.Visible = False
Windows("ChartsChuck4.xls").Activate
Range("N20").Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("N20"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(2, 1), Array(10, 1), Array(12,
1), Array(20, 1), _
Array(22, 1), Array(30, 1), Array(32, 1))
Range("N24").Select
End Sub

Any help would be much appreciated...........
Vaya con Dios,
Chuck, CABGx3

[Ron Rosenfeld]

On Sun, 6 Feb 2005 12:05:07 -0500, "CLR" <[email protected]> wrote:

>If someone would please be so kind..........I am in need of code to extract
>the formula from the Text Box that is put there when one creates an XY
>Scatter chart and adds a Third-order Polynomial Trendline.

David Braden did this a few years ago. The code is below. Read the notes
carefully and be sure to note that the values extracted will have the same
precision as the values displayed on the chart. So you will probably want to
set the format to a high precision, as he suggests.

=============================================
Option Explicit
'As J.W. Lewis has noted, Excel's Chart Trendline function yields
'exceptionally good values for the models it fits. In contrast to
'Excel's overall stats-capability, Trendline is a standout.
'
'These functions provide a quasi-dynamic link to a chart's *displayed*
'trendline to help avoid deficencies of Excel's LINEST.
'
'Function TLcoef(...) returns Trendline coefficients
'Function TLeval(x, ...) evaluates the current trendline at a given x
'
'To specify the arguments of TLcoef, and the last 4 of TLeval:
' vSheet is the name/number of the sheet containing the chart.
' I strongly recommend you use the text name appearing in the Sheet 's tab
' 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
' the standard toolbar.
' If there is only one chart in the sheet, you can safely use just 1 as an
' argument.
' VSeries is a series name/number, and vTL is the series' trendline number.
' Ideally you will have named the series, and refer to it by name.
' To determine its 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.
'
'David J. Braden maintains this as an open-community effort. Plz post or send
' suggestions to him.

'First draft written 2003 March 1 by D J Braden

'Current concern(s)
' (1) Because this is a function, we can't reliably get the underlying
Trendline
' coefficients to greater accuracy than what is 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)
' (2) Even though the functions are volatile, you may have to do a Worksheet
' recalc to get things updated properly for anything changing the chart to
' get passed through to these functions. (

'********************************************************

Const cFirstNumPos = 5 ' pos. of first integer in displayed eqn
Const cMaxFormat = "0.00000000000000E+00"

Function TLcoef(vSheet, vCht, vSeries, vTL)

'Return coefficients of an Excel chart trendline, *to precision displayed*
'
'Note: While Trendline seemingly always reports subsequent terms from
'a given one on, sometimes it reduces the order of the fit. So this function
'returns, for a poly-fit, an array of length 1 + the order of the requested
fit,
' *not* the number of values displayed. The last value in the return 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 GoTo HanErr
Set o = Sheets(vSheet).ChartObjects(vCht).Chart. _
SeriesCollection(vSeries).Trendlines(vTL)
TLcoef = ExtractCoef(o, cFirstNumPos)
Exit Function

HanErr:
TLcoef = CVErr(xlErrValue)
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 Not CheckNum(vX, TLeval) Then Exit Function
If ParamErr(TLeval, vSheet, vCht, vSeries, vTL) Then Exit Function

Set o = Sheets(vSheet).ChartObjects(vCht).Chart.SeriesCollection(vSeries) _
.Trendlines(vTL)

vRet = ExtractCoef(o, cFirstNumPos)
Select Case o.Type
Case xlLinear
vRet(1) = vX * vRet(1) + vRet(2)
Case xlExponential 'see comment above
vRet(1) = Exp(Log(vRet(1)) + vX * vRet(2))
Case xlLogarithmic
vRet(1) = vRet(1) * Log(vX) + vRet(2)
Case xlPower 'see comment above
vRet(1) = Exp(Log(vRet(1)) + Log(vX) * vRet(2))
Case xlPolynomial
Dim l As Long
vRet(1) = vRet(1) * vX + vRet(2)
For l = 3 To UBound(vRet)
vRet(1) = vX * vRet(1) + vRet(l)
Next
End Select
TLeval = vRet(1)
Exit Function

HanErr:
TLeval = CVErr(xlErrValue)
End Function

Private Function ExtractCoef(o As Trendline, ByVal lLastPos As Long)
Dim lCurPos As Long, s As String

s = o.DataLabel.Text

If o.DisplayRSquared Then
lCurPos = InStr(s, "R")
s = Left$(s, lCurPos - 1)
End If

If o.Type <> xlPolynomial Then
ReDim v(1 To 2) As Double

If o.Type = xlExponential Then
s = Application.WorksheetFunction.Substitute(s, "x", "")
s = Application.WorksheetFunction.Substitute(s, "e", "x")
ElseIf o.Type = xlLogarithmic Then
s = Application.WorksheetFunction.Substitute(s, "Ln(x)", "x")
End If

lCurPos = InStr(1, s, "x")
If lCurPos = 0 Then
v(2) = Mid(s, lLastPos)
Else
v(1) = Mid(s, lLastPos, lCurPos - lLastPos)
v(2) = Mid(s, lCurPos + 1)
End If

Else 'have a polynomial
Dim lOrd As Long
ReDim v(1 To o.Order + 1) As Double

lCurPos = InStr(s, "x")
If lCurPos = 0 Then
v(o.Order + 1) = Mid(s, lLastPos)
Exit Function 'with single constant term
End If
'else
lOrd = Mid(s, lCurPos + 1, 1)
Do While lOrd > 1
v(UBound(v) - lOrd) = Mid(s, lLastPos, lCurPos - lLastPos)
lLastPos = lCurPos + 2
lCurPos = InStr(lLastPos, s, "x")
lOrd = lOrd - 1
Loop
'peel off coeffs. for affine terms in eqn
v(o.Order) = Mid(s, lLastPos, lCurPos - lLastPos)
v(o.Order + 1) = Mid(s, lCurPos + 1)
End If
ExtractCoef = v
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
End Function
=====================================

--ron

[Ron Rosenfeld]

On Sun, 6 Feb 2005 12:05:07 -0500, "CLR" <[email protected]> wrote:

>Hi All.........
>
>If someone would please be so kind..........I am in need of code to extract
>the formula from the Text Box that is put there when one creates an XY
>Scatter chart and adds a Third-order Polynomial Trendline. I am trying to
>create a macro that will do this automatically and then do normal
>Text-to-columns and break it apart and then do the math.........I can get it
>all working by recording the macro, but for some reason I can't "get" the
>formula out of the text box, only by Cntrl-C have I been able to copy and
>paste it to a cell, and that doesn't "record" too well.........I know about
>the LINEST function, but for some reason it's answer does not jive with that
>from the formula from the text box...........my user wants to use the
>formula from the text box.
>

With regard to David Braden's code which I just posted, you will need to edit
the word-wrap problems.
--ron

[CLR]

Thanks Ron.............I have that from David already, but with my limited
ability, could not figure out how to use it..........(I recognized the
word-wrap thing and attempted to correct it)..........but I still don't
know how to use the functions.

Besides, it appears to be doing the math rather than just obtaining the TEXT
version of the formula from the Text Box, which is what I am trying to
do.......I can get it by selecting the box with the mouse and then
highlighting the formula, then Control-C, but that step does not "record" on
a macro and I don't know how to code it.

Here's the raw data my user is Charting, and looking to find the "B" value
for an "A" of 33660.
The 3rd Order Poly Trendline gives a text box with this formula......(y =
5E-12x3 - 2E-07x2 + 0.0027x - 3.101)..........this is the one I'm trying to
extract from the box.

A B
5610 7
11550 10
16830 12
22110 16
26600 26
33660 ?


Thanks again for your time..........
Vaya con Dios,
Chuck, CABGx3


"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Sun, 6 Feb 2005 12:05:07 -0500, "CLR" <[email protected]> wrote:
>
> >If someone would please be so kind..........I am in need of code to
extract
> >the formula from the Text Box that is put there when one creates an XY
> >Scatter chart and adds a Third-order Polynomial Trendline.
>
> David Braden did this a few years ago. The code is below. Read the notes
> carefully and be sure to note that the values extracted will have the same
> precision as the values displayed on the chart. So you will probably want
to
> set the format to a high precision, as he suggests.
>
> =============================================
> Option Explicit
> 'As J.W. Lewis has noted, Excel's Chart Trendline function yields
> 'exceptionally good values for the models it fits. In contrast to
> 'Excel's overall stats-capability, Trendline is a standout.
> '
> 'These functions provide a quasi-dynamic link to a chart's *displayed*
> 'trendline to help avoid deficencies of Excel's LINEST.
> '
> 'Function TLcoef(...) returns Trendline coefficients
> 'Function TLeval(x, ...) evaluates the current trendline at a given x
> '
> 'To specify the arguments of TLcoef, and the last 4 of TLeval:
> ' vSheet is the name/number of the sheet containing the chart.
> ' I strongly recommend you use the text name appearing in the Sheet 's
tab
> ' 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
> ' the standard toolbar.
> ' If there is only one chart in the sheet, you can safely use just 1
as an
> ' argument.
> ' VSeries is a series name/number, and vTL is the series' trendline
number.
> ' Ideally you will have named the series, and refer to it by name.
> ' To determine its 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.
> '
> 'David J. Braden maintains this as an open-community effort. Plz post or
send
> ' suggestions to him.
>
> 'First draft written 2003 March 1 by D J Braden
>
> 'Current concern(s)
> ' (1) Because this is a function, we can't reliably get the underlying
> Trendline
> ' coefficients to greater accuracy than what is 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)
> ' (2) Even though the functions are volatile, you may have to do a
Worksheet
> ' recalc to get things updated properly for anything changing the chart
to
> ' get passed through to these functions. (
>
> '********************************************************
>
> Const cFirstNumPos = 5 ' pos. of first integer in displayed eqn
> Const cMaxFormat = "0.00000000000000E+00"
>
> Function TLcoef(vSheet, vCht, vSeries, vTL)
>
> 'Return coefficients of an Excel chart trendline, *to precision displayed*
> '
> 'Note: While Trendline seemingly always reports subsequent terms from
> 'a given one on, sometimes it reduces the order of the fit. So this
function
> 'returns, for a poly-fit, an array of length 1 + the order of the
requested
> fit,
> ' *not* the number of values displayed. The last value in the return
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 GoTo HanErr
> Set o = Sheets(vSheet).ChartObjects(vCht).Chart. _
> SeriesCollection(vSeries).Trendlines(vTL)
> TLcoef = ExtractCoef(o, cFirstNumPos)
> Exit Function
>
> HanErr:
> TLcoef = CVErr(xlErrValue)
> 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 Not CheckNum(vX, TLeval) Then Exit Function
> If ParamErr(TLeval, vSheet, vCht, vSeries, vTL) Then Exit Function
>
> Set o =
Sheets(vSheet).ChartObjects(vCht).Chart.SeriesCollection(vSeries) _
> .Trendlines(vTL)
>
> vRet = ExtractCoef(o, cFirstNumPos)
> Select Case o.Type
> Case xlLinear
> vRet(1) = vX * vRet(1) + vRet(2)
> Case xlExponential 'see comment above
> vRet(1) = Exp(Log(vRet(1)) + vX * vRet(2))
> Case xlLogarithmic
> vRet(1) = vRet(1) * Log(vX) + vRet(2)
> Case xlPower 'see comment above
> vRet(1) = Exp(Log(vRet(1)) + Log(vX) * vRet(2))
> Case xlPolynomial
> Dim l As Long
> vRet(1) = vRet(1) * vX + vRet(2)
> For l = 3 To UBound(vRet)
> vRet(1) = vX * vRet(1) + vRet(l)
> Next
> End Select
> TLeval = vRet(1)
> Exit Function
>
> HanErr:
> TLeval = CVErr(xlErrValue)
> End Function
>
> Private Function ExtractCoef(o As Trendline, ByVal lLastPos As Long)
> Dim lCurPos As Long, s As String
>
> s = o.DataLabel.Text
>
> If o.DisplayRSquared Then
> lCurPos = InStr(s, "R")
> s = Left$(s, lCurPos - 1)
> End If
>
> If o.Type <> xlPolynomial Then
> ReDim v(1 To 2) As Double
>
> If o.Type = xlExponential Then
> s = Application.WorksheetFunction.Substitute(s, "x", "")
> s = Application.WorksheetFunction.Substitute(s, "e", "x")
> ElseIf o.Type = xlLogarithmic Then
> s = Application.WorksheetFunction.Substitute(s, "Ln(x)", "x")
> End If
>
> lCurPos = InStr(1, s, "x")
> If lCurPos = 0 Then
> v(2) = Mid(s, lLastPos)
> Else
> v(1) = Mid(s, lLastPos, lCurPos - lLastPos)
> v(2) = Mid(s, lCurPos + 1)
> End If
>
> Else 'have a polynomial
> Dim lOrd As Long
> ReDim v(1 To o.Order + 1) As Double
>
> lCurPos = InStr(s, "x")
> If lCurPos = 0 Then
> v(o.Order + 1) = Mid(s, lLastPos)
> Exit Function 'with single constant term
> End If
> 'else
> lOrd = Mid(s, lCurPos + 1, 1)
> Do While lOrd > 1
> v(UBound(v) - lOrd) = Mid(s, lLastPos, lCurPos - lLastPos)
> lLastPos = lCurPos + 2
> lCurPos = InStr(lLastPos, s, "x")
> lOrd = lOrd - 1
> Loop
> 'peel off coeffs. for affine terms in eqn
> v(o.Order) = Mid(s, lLastPos, lCurPos - lLastPos)
> v(o.Order + 1) = Mid(s, lCurPos + 1)
> End If
> ExtractCoef = v
> 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
> End Function
> =====================================
>
> --ron

[Tom Ogilvy]

Here is one I wrote for someone about a year ago.
I marked the line that gets the formula. It goes on to break the
coefficents out and place them in separate cells starting in N6.

Sub GetFormula()
Dim sStr As String, sStr1 As String
Dim sFormula As String, j As Long
Dim i As Long
Dim ser As Series, sChar As String
Dim tLine As Trendline
Dim cht As Chart
Dim rng As Range
Dim varr()
ReDim varr(1 To 10)
Set cht = ActiveSheet.ChartObjects(1).Chart
For Each ser In cht.SeriesCollection
If ser.Trendlines.Count = 1 Then
Set tLine = ser.Trendlines(1)
If tLine.DisplayEquation Then
sFormula = tLine.DataLabel.Text '<== this gets the formula
sFormula = Application.Substitute(sFormula, _
"y = ", "")
sFormula = Application.Substitute(sFormula, _
" + ", ",")
'Debug.Print sFormula
j = 1
For i = 1 To Len(sFormula)
sChar = Mid(sFormula, i, 1)
If sChar = "," Or i = Len(sFormula) Then
If i = Len(sFormula) Then
sStr1 = sStr1 & sChar
End If
varr(j) = sStr1
sStr1 = ""
j = j + 1
Else
sStr1 = sStr1 & sChar
End If
Next
ReDim Preserve varr(1 To j - 1)
Set rng = Range("N6")
j = 1
For i = LBound(varr) To UBound(varr)
rng(j).Value = Val(varr(i))
j = j + 1
Next i
Exit Sub
End If
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"CLR" <[email protected]> wrote in message
news:[email protected]...
> Thanks Ron.............I have that from David already, but with my limited
> ability, could not figure out how to use it..........(I recognized the
> word-wrap thing and attempted to correct it)..........but I still don't
> know how to use the functions.
>
> Besides, it appears to be doing the math rather than just obtaining the
TEXT
> version of the formula from the Text Box, which is what I am trying to
> do.......I can get it by selecting the box with the mouse and then
> highlighting the formula, then Control-C, but that step does not "record"
on
> a macro and I don't know how to code it.
>
> Here's the raw data my user is Charting, and looking to find the "B" value
> for an "A" of 33660.
> The 3rd Order Poly Trendline gives a text box with this formula......(y =
> 5E-12x3 - 2E-07x2 + 0.0027x - 3.101)..........this is the one I'm trying
to
> extract from the box.
>
> A B
> 5610 7
> 11550 10
> 16830 12
> 22110 16
> 26600 26
> 33660 ?
>
>
> Thanks again for your time..........
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Ron Rosenfeld" <[email protected]> wrote in message
> news:[email protected]...
> > On Sun, 6 Feb 2005 12:05:07 -0500, "CLR" <[email protected]>
wrote:
> >
> > >If someone would please be so kind..........I am in need of code to
> extract
> > >the formula from the Text Box that is put there when one creates an XY
> > >Scatter chart and adds a Third-order Polynomial Trendline.
> >
> > David Braden did this a few years ago. The code is below. Read the
notes
> > carefully and be sure to note that the values extracted will have the
same
> > precision as the values displayed on the chart. So you will probably
want
> to
> > set the format to a high precision, as he suggests.
> >
> > =============================================
> > Option Explicit
> > 'As J.W. Lewis has noted, Excel's Chart Trendline function yields
> > 'exceptionally good values for the models it fits. In contrast to
> > 'Excel's overall stats-capability, Trendline is a standout.
> > '
> > 'These functions provide a quasi-dynamic link to a chart's *displayed*
> > 'trendline to help avoid deficencies of Excel's LINEST.
> > '
> > 'Function TLcoef(...) returns Trendline coefficients
> > 'Function TLeval(x, ...) evaluates the current trendline at a given x
> > '
> > 'To specify the arguments of TLcoef, and the last 4 of TLeval:
> > ' vSheet is the name/number of the sheet containing the chart.
> > ' I strongly recommend you use the text name appearing in the Sheet
's
> tab
> > ' 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
> > ' the standard toolbar.
> > ' If there is only one chart in the sheet, you can safely use just 1
> as an
> > ' argument.
> > ' VSeries is a series name/number, and vTL is the series' trendline
> number.
> > ' Ideally you will have named the series, and refer to it by name.
> > ' To determine its 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.
> > '
> > 'David J. Braden maintains this as an open-community effort. Plz post
or
> send
> > ' suggestions to him.
> >
> > 'First draft written 2003 March 1 by D J Braden
> >
> > 'Current concern(s)
> > ' (1) Because this is a function, we can't reliably get the underlying
> > Trendline
> > ' coefficients to greater accuracy than what is 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)
> > ' (2) Even though the functions are volatile, you may have to do a
> Worksheet
> > ' recalc to get things updated properly for anything changing the
chart
> to
> > ' get passed through to these functions. (
> >
> > '********************************************************
> >
> > Const cFirstNumPos = 5 ' pos. of first integer in displayed eqn
> > Const cMaxFormat = "0.00000000000000E+00"
> >
> > Function TLcoef(vSheet, vCht, vSeries, vTL)
> >
> > 'Return coefficients of an Excel chart trendline, *to precision
displayed*
> > '
> > 'Note: While Trendline seemingly always reports subsequent terms from
> > 'a given one on, sometimes it reduces the order of the fit. So this
> function
> > 'returns, for a poly-fit, an array of length 1 + the order of the
> requested
> > fit,
> > ' *not* the number of values displayed. The last value in the return
> 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 GoTo HanErr
> > Set o = Sheets(vSheet).ChartObjects(vCht).Chart. _
> > SeriesCollection(vSeries).Trendlines(vTL)
> > TLcoef = ExtractCoef(o, cFirstNumPos)
> > Exit Function
> >
> > HanErr:
> > TLcoef = CVErr(xlErrValue)
> > 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 Not CheckNum(vX, TLeval) Then Exit Function
> > If ParamErr(TLeval, vSheet, vCht, vSeries, vTL) Then Exit Function
> >
> > Set o =
> Sheets(vSheet).ChartObjects(vCht).Chart.SeriesCollection(vSeries) _
> > .Trendlines(vTL)
> >
> > vRet = ExtractCoef(o, cFirstNumPos)
> > Select Case o.Type
> > Case xlLinear
> > vRet(1) = vX * vRet(1) + vRet(2)
> > Case xlExponential 'see comment above
> > vRet(1) = Exp(Log(vRet(1)) + vX * vRet(2))
> > Case xlLogarithmic
> > vRet(1) = vRet(1) * Log(vX) + vRet(2)
> > Case xlPower 'see comment above
> > vRet(1) = Exp(Log(vRet(1)) + Log(vX) * vRet(2))
> > Case xlPolynomial
> > Dim l As Long
> > vRet(1) = vRet(1) * vX + vRet(2)
> > For l = 3 To UBound(vRet)
> > vRet(1) = vX * vRet(1) + vRet(l)
> > Next
> > End Select
> > TLeval = vRet(1)
> > Exit Function
> >
> > HanErr:
> > TLeval = CVErr(xlErrValue)
> > End Function
> >
> > Private Function ExtractCoef(o As Trendline, ByVal lLastPos As Long)
> > Dim lCurPos As Long, s As String
> >
> > s = o.DataLabel.Text
> >
> > If o.DisplayRSquared Then
> > lCurPos = InStr(s, "R")
> > s = Left$(s, lCurPos - 1)
> > End If
> >
> > If o.Type <> xlPolynomial Then
> > ReDim v(1 To 2) As Double
> >
> > If o.Type = xlExponential Then
> > s = Application.WorksheetFunction.Substitute(s, "x", "")
> > s = Application.WorksheetFunction.Substitute(s, "e", "x")
> > ElseIf o.Type = xlLogarithmic Then
> > s = Application.WorksheetFunction.Substitute(s, "Ln(x)", "x")
> > End If
> >
> > lCurPos = InStr(1, s, "x")
> > If lCurPos = 0 Then
> > v(2) = Mid(s, lLastPos)
> > Else
> > v(1) = Mid(s, lLastPos, lCurPos - lLastPos)
> > v(2) = Mid(s, lCurPos + 1)
> > End If
> >
> > Else 'have a polynomial
> > Dim lOrd As Long
> > ReDim v(1 To o.Order + 1) As Double
> >
> > lCurPos = InStr(s, "x")
> > If lCurPos = 0 Then
> > v(o.Order + 1) = Mid(s, lLastPos)
> > Exit Function 'with single constant term
> > End If
> > 'else
> > lOrd = Mid(s, lCurPos + 1, 1)
> > Do While lOrd > 1
> > v(UBound(v) - lOrd) = Mid(s, lLastPos, lCurPos - lLastPos)
> > lLastPos = lCurPos + 2
> > lCurPos = InStr(lLastPos, s, "x")
> > lOrd = lOrd - 1
> > Loop
> > 'peel off coeffs. for affine terms in eqn
> > v(o.Order) = Mid(s, lLastPos, lCurPos - lLastPos)
> > v(o.Order + 1) = Mid(s, lCurPos + 1)
> > End If
> > ExtractCoef = v
> > 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
> > End Function
> > =====================================
> >
> > --ron
>
>

[Ron Rosenfeld]

On Sun, 6 Feb 2005 13:57:35 -0500, "CLR" <[email protected]> wrote:

>Thanks Ron.............I have that from David already, but with my limited
>ability, could not figure out how to use it..........(I recognized the
>word-wrap thing and attempted to correct it)..........but I still don't
>know how to use the functions.
>
>Besides, it appears to be doing the math rather than just obtaining the TEXT
>version of the formula from the Text Box, which is what I am trying to
>do.......I can get it by selecting the box with the mouse and then
>highlighting the formula, then Control-C, but that step does not "record" on
>a macro and I don't know how to code it.
>
>Here's the raw data my user is Charting, and looking to find the "B" value
>for an "A" of 33660.
>The 3rd Order Poly Trendline gives a text box with this formula......(y =
>5E-12x3 - 2E-07x2 + 0.0027x - 3.101)..........this is the one I'm trying to
>extract from the box.
>
> A B
>5610 7
>11550 10
>16830 12
>22110 16
>26600 26
>33660 ?
>
>
>Thanks again for your time..........
>Vaya con Dios,
>Chuck, CABGx3

Did you see the part where the UDF has to be entered as an array formula across
enough cells to show all the coefficients? Although he doesn't mention it, the
TLcoef UDF has to be entered as a *horizontal* array (although you could use
TRANSPOSE if you needed a vertical array).

Also, you need to be sure to use the correct arguments. But he's got that
described in his sheet.

For TLCoef he *IS* extracting the text. He is NOT doing the calculations.
That's why you have to first set the format of the trendline coefficients to a
high level of precision. For example, with your data, and using the formula

=TLcoef("Sheet1",1,1,1)

I get the following coefficients:

4.934559263250230E-12
-1.923480383365620E-07
2.716099808316560E-03
-3.101037739059700E+00

The first multiplied by the x^3; the next by x^2, and so forth.

For the new x of 33660, I get a value of 5.858087089649060E+01 or about 58.58

However, one could also use Bradens TLEval formula which does those
calculations for you:

=TLeval(A6,"Sheet1",1,1,1)

(where 33660 is in A6) and obtain the same result:

5.858087089649060E+01

By the way, using LINEST, which apparently does not use as good an algorithym
as does the trendline function on the chart, one gets the slightly different
answer of:

5.858087089649120E+01

The formula for the above is

=SUMPRODUCT(A6^{3,2,1,0},LINEST(B1:B5,A1:A5^{1,2,3}))

with your data in A1:B5 and your new 'x' in A6.

Try David's formula again with the above in mind, and let me know if you can
get it working for you.


--ron

[Peter T]

Hi Chuck,

Another one, just for fun.

Sub Test()

''''''''''''''''''
Dim ch As ChartObject
On Error Resume Next
Set ch = ActiveSheet.ChartObjects("TestChart") '.Chart
On Error GoTo 0
If ch Is Nothing Then
With ActiveSheet.ChartObjects.Add(10, 10, 400, 200)
.Chart.ChartType = xlXYScatter
With .Chart.SeriesCollection.NewSeries
.Formula = _
"=SERIES(,{5610,11550,16830,22110,26600},{7,10,12,16,26},)"
End With
.Chart.ChartArea.Font.Size = 10
.Name = "TestChart"
.Select
End With
End If

''''''''''''''

Dim sEqu As String, sFmla As String
Dim A As Double, B As Double

With ActiveChart.SeriesCollection(1).Trendlines.Add
.Type = xlPolynomial
.Order = 3
.DisplayEquation = True
sEqu = .DataLabel.Text
'maybe uncomment the Delete's first run
.DataLabel.Delete
.Delete
End With

A = 33660
With Application
sFmla = .Substitute(sEqu, "y = ", "")
sFmla = .Substitute(sFmla, "x3", "*" & A & "^3")
sFmla = .Substitute(sFmla, "x2", "*" & A & "^2")
sFmla = .Substitute(sFmla, "x", "*" & A)
End With
B = Evaluate(sFmla)
'Debug.Print sEqu, A; B
MsgBox sEqu & vbCr & "A " & A & vbCr & "B " & B

End Sub

Regards,
Peter T

"CLR" <[email protected]> wrote in message
news:[email protected]...
> Thanks Ron.............I have that from David already, but with my limited
> ability, could not figure out how to use it..........(I recognized the
> word-wrap thing and attempted to correct it)..........but I still don't
> know how to use the functions.
>
> Besides, it appears to be doing the math rather than just obtaining the
TEXT
> version of the formula from the Text Box, which is what I am trying to
> do.......I can get it by selecting the box with the mouse and then
> highlighting the formula, then Control-C, but that step does not "record"
on
> a macro and I don't know how to code it.
>
> Here's the raw data my user is Charting, and looking to find the "B" value
> for an "A" of 33660.
> The 3rd Order Poly Trendline gives a text box with this formula......(y =
> 5E-12x3 - 2E-07x2 + 0.0027x - 3.101)..........this is the one I'm trying
to
> extract from the box.
>
> A B
> 5610 7
> 11550 10
> 16830 12
> 22110 16
> 26600 26
> 33660 ?
>
>
> Thanks again for your time..........
> Vaya con Dios,
> Chuck, CABGx3
>

[CLR]

Hi Tom..........

Thanks, but I must have done something wrong.............all I got by
running it was the first coefficient in N6 and the third coefficient in
N7.........no complete formula anywhere..........I'll give it a better look
tomorrow, as it's nearing my bedtime and my thinking-cap is starting to
slip......

Thanks again,
Vaya con Dios,
Chuck, CABGx3


"Tom Ogilvy" <[email protected]> wrote in message
news:#[email protected]...
> Here is one I wrote for someone about a year ago.
> I marked the line that gets the formula. It goes on to break the
> coefficents out and place them in separate cells starting in N6.
>
> Sub GetFormula()
> Dim sStr As String, sStr1 As String
> Dim sFormula As String, j As Long
> Dim i As Long
> Dim ser As Series, sChar As String
> Dim tLine As Trendline
> Dim cht As Chart
> Dim rng As Range
> Dim varr()
> ReDim varr(1 To 10)
> Set cht = ActiveSheet.ChartObjects(1).Chart
> For Each ser In cht.SeriesCollection
> If ser.Trendlines.Count = 1 Then
> Set tLine = ser.Trendlines(1)
> If tLine.DisplayEquation Then
> sFormula = tLine.DataLabel.Text '<== this gets the formula
> sFormula = Application.Substitute(sFormula, _
> "y = ", "")
> sFormula = Application.Substitute(sFormula, _
> " + ", ",")
> 'Debug.Print sFormula
> j = 1
> For i = 1 To Len(sFormula)
> sChar = Mid(sFormula, i, 1)
> If sChar = "," Or i = Len(sFormula) Then
> If i = Len(sFormula) Then
> sStr1 = sStr1 & sChar
> End If
> varr(j) = sStr1
> sStr1 = ""
> j = j + 1
> Else
> sStr1 = sStr1 & sChar
> End If
> Next
> ReDim Preserve varr(1 To j - 1)
> Set rng = Range("N6")
> j = 1
> For i = LBound(varr) To UBound(varr)
> rng(j).Value = Val(varr(i))
> j = j + 1
> Next i
> Exit Sub
> End If
> End If
> Next
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Ron.............I have that from David already, but with my
limited
> > ability, could not figure out how to use it..........(I recognized the
> > word-wrap thing and attempted to correct it)..........but I still don't
> > know how to use the functions.
> >
> > Besides, it appears to be doing the math rather than just obtaining the
> TEXT
> > version of the formula from the Text Box, which is what I am trying to
> > do.......I can get it by selecting the box with the mouse and then
> > highlighting the formula, then Control-C, but that step does not
"record"
> on
> > a macro and I don't know how to code it.
> >
> > Here's the raw data my user is Charting, and looking to find the "B"
value
> > for an "A" of 33660.
> > The 3rd Order Poly Trendline gives a text box with this formula......(y
=
> > 5E-12x3 - 2E-07x2 + 0.0027x - 3.101)..........this is the one I'm trying
> to
> > extract from the box.
> >
> > A B
> > 5610 7
> > 11550 10
> > 16830 12
> > 22110 16
> > 26600 26
> > 33660 ?
> >
> >
> > Thanks again for your time..........
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Ron Rosenfeld" <[email protected]> wrote in message
> > news:[email protected]...
> > > On Sun, 6 Feb 2005 12:05:07 -0500, "CLR" <[email protected]>
> wrote:
> > >
> > > >If someone would please be so kind..........I am in need of code to
> > extract
> > > >the formula from the Text Box that is put there when one creates an
XY
> > > >Scatter chart and adds a Third-order Polynomial Trendline.
> > >
> > > David Braden did this a few years ago. The code is below. Read the
> notes
> > > carefully and be sure to note that the values extracted will have the
> same
> > > precision as the values displayed on the chart. So you will probably
> want
> > to
> > > set the format to a high precision, as he suggests.
> > >
> > > =============================================
> > > Option Explicit
> > > 'As J.W. Lewis has noted, Excel's Chart Trendline function yields
> > > 'exceptionally good values for the models it fits. In contrast to
> > > 'Excel's overall stats-capability, Trendline is a standout.
> > > '
> > > 'These functions provide a quasi-dynamic link to a chart's *displayed*
> > > 'trendline to help avoid deficencies of Excel's LINEST.
> > > '
> > > 'Function TLcoef(...) returns Trendline coefficients
> > > 'Function TLeval(x, ...) evaluates the current trendline at a given x
> > > '
> > > 'To specify the arguments of TLcoef, and the last 4 of TLeval:
> > > ' vSheet is the name/number of the sheet containing the chart.
> > > ' I strongly recommend you use the text name appearing in the
Sheet
> 's
> > tab
> > > ' 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
> > > ' the standard toolbar.
> > > ' If there is only one chart in the sheet, you can safely use just
1
> > as an
> > > ' argument.
> > > ' VSeries is a series name/number, and vTL is the series' trendline
> > number.
> > > ' Ideally you will have named the series, and refer to it by
name.
> > > ' To determine its 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.
> > > '
> > > 'David J. Braden maintains this as an open-community effort. Plz post
> or
> > send
> > > ' suggestions to him.
> > >
> > > 'First draft written 2003 March 1 by D J Braden
> > >
> > > 'Current concern(s)
> > > ' (1) Because this is a function, we can't reliably get the
underlying
> > > Trendline
> > > ' coefficients to greater accuracy than what is 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)
> > > ' (2) Even though the functions are volatile, you may have to do a
> > Worksheet
> > > ' recalc to get things updated properly for anything changing the
> chart
> > to
> > > ' get passed through to these functions. (
> > >
> > > '********************************************************
> > >
> > > Const cFirstNumPos = 5 ' pos. of first integer in displayed eqn
> > > Const cMaxFormat = "0.00000000000000E+00"
> > >
> > > Function TLcoef(vSheet, vCht, vSeries, vTL)
> > >
> > > 'Return coefficients of an Excel chart trendline, *to precision
> displayed*
> > > '
> > > 'Note: While Trendline seemingly always reports subsequent terms from
> > > 'a given one on, sometimes it reduces the order of the fit. So this
> > function
> > > 'returns, for a poly-fit, an array of length 1 + the order of the
> > requested
> > > fit,
> > > ' *not* the number of values displayed. The last value in the return
> > 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 GoTo HanErr
> > > Set o = Sheets(vSheet).ChartObjects(vCht).Chart. _
> > > SeriesCollection(vSeries).Trendlines(vTL)
> > > TLcoef = ExtractCoef(o, cFirstNumPos)
> > > Exit Function
> > >
> > > HanErr:
> > > TLcoef = CVErr(xlErrValue)
> > > 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 Not CheckNum(vX, TLeval) Then Exit Function
> > > If ParamErr(TLeval, vSheet, vCht, vSeries, vTL) Then Exit Function
> > >
> > > Set o =
> > Sheets(vSheet).ChartObjects(vCht).Chart.SeriesCollection(vSeries) _
> > > .Trendlines(vTL)
> > >
> > > vRet = ExtractCoef(o, cFirstNumPos)
> > > Select Case o.Type
> > > Case xlLinear
> > > vRet(1) = vX * vRet(1) + vRet(2)
> > > Case xlExponential 'see comment above
> > > vRet(1) = Exp(Log(vRet(1)) + vX * vRet(2))
> > > Case xlLogarithmic
> > > vRet(1) = vRet(1) * Log(vX) + vRet(2)
> > > Case xlPower 'see comment above
> > > vRet(1) = Exp(Log(vRet(1)) + Log(vX) * vRet(2))
> > > Case xlPolynomial
> > > Dim l As Long
> > > vRet(1) = vRet(1) * vX + vRet(2)
> > > For l = 3 To UBound(vRet)
> > > vRet(1) = vX * vRet(1) + vRet(l)
> > > Next
> > > End Select
> > > TLeval = vRet(1)
> > > Exit Function
> > >
> > > HanErr:
> > > TLeval = CVErr(xlErrValue)
> > > End Function
> > >
> > > Private Function ExtractCoef(o As Trendline, ByVal lLastPos As Long)
> > > Dim lCurPos As Long, s As String
> > >
> > > s = o.DataLabel.Text
> > >
> > > If o.DisplayRSquared Then
> > > lCurPos = InStr(s, "R")
> > > s = Left$(s, lCurPos - 1)
> > > End If
> > >
> > > If o.Type <> xlPolynomial Then
> > > ReDim v(1 To 2) As Double
> > >
> > > If o.Type = xlExponential Then
> > > s = Application.WorksheetFunction.Substitute(s, "x", "")
> > > s = Application.WorksheetFunction.Substitute(s, "e", "x")
> > > ElseIf o.Type = xlLogarithmic Then
> > > s = Application.WorksheetFunction.Substitute(s, "Ln(x)", "x")
> > > End If
> > >
> > > lCurPos = InStr(1, s, "x")
> > > If lCurPos = 0 Then
> > > v(2) = Mid(s, lLastPos)
> > > Else
> > > v(1) = Mid(s, lLastPos, lCurPos - lLastPos)
> > > v(2) = Mid(s, lCurPos + 1)
> > > End If
> > >
> > > Else 'have a polynomial
> > > Dim lOrd As Long
> > > ReDim v(1 To o.Order + 1) As Double
> > >
> > > lCurPos = InStr(s, "x")
> > > If lCurPos = 0 Then
> > > v(o.Order + 1) = Mid(s, lLastPos)
> > > Exit Function 'with single constant term
> > > End If
> > > 'else
> > > lOrd = Mid(s, lCurPos + 1, 1)
> > > Do While lOrd > 1
> > > v(UBound(v) - lOrd) = Mid(s, lLastPos, lCurPos - lLastPos)
> > > lLastPos = lCurPos + 2
> > > lCurPos = InStr(lLastPos, s, "x")
> > > lOrd = lOrd - 1
> > > Loop
> > > 'peel off coeffs. for affine terms in eqn
> > > v(o.Order) = Mid(s, lLastPos, lCurPos - lLastPos)
> > > v(o.Order + 1) = Mid(s, lCurPos + 1)
> > > End If
> > > ExtractCoef = v
> > > 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
> > > End Function
> > > =====================================
> > >
> > > --ron
> >
> >
>
>

[CLR]

Thanks Ron..............

All I get tonight is "Compile Errors", and #VALUE! in the cells for both
functions........I guess I'll have to work on it a bit more tomorrow when
I'm not so sleepy...........

Thanks again,
Vaya con Dios,
Chuck, CABGx3


"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Sun, 6 Feb 2005 13:57:35 -0500, "CLR" <[email protected]> wrote:
>
> >Thanks Ron.............I have that from David already, but with my
limited
> >ability, could not figure out how to use it..........(I recognized the
> >word-wrap thing and attempted to correct it)..........but I still don't
> >know how to use the functions.
> >
> >Besides, it appears to be doing the math rather than just obtaining the
TEXT
> >version of the formula from the Text Box, which is what I am trying to
> >do.......I can get it by selecting the box with the mouse and then
> >highlighting the formula, then Control-C, but that step does not "record"
on
> >a macro and I don't know how to code it.
> >
> >Here's the raw data my user is Charting, and looking to find the "B"
value
> >for an "A" of 33660.
> >The 3rd Order Poly Trendline gives a text box with this formula......(y =
> >5E-12x3 - 2E-07x2 + 0.0027x - 3.101)..........this is the one I'm trying
to
> >extract from the box.
> >
> > A B
> >5610 7
> >11550 10
> >16830 12
> >22110 16
> >26600 26
> >33660 ?
> >
> >
> >Thanks again for your time..........
> >Vaya con Dios,
> >Chuck, CABGx3
>
> Did you see the part where the UDF has to be entered as an array formula
across
> enough cells to show all the coefficients? Although he doesn't mention
it, the
> TLcoef UDF has to be entered as a *horizontal* array (although you could
use
> TRANSPOSE if you needed a vertical array).
>
> Also, you need to be sure to use the correct arguments. But he's got that
> described in his sheet.
>
> For TLCoef he *IS* extracting the text. He is NOT doing the calculations.
> That's why you have to first set the format of the trendline coefficients
to a
> high level of precision. For example, with your data, and using the
formula
>
> =TLcoef("Sheet1",1,1,1)
>
> I get the following coefficients:
>
> 4.934559263250230E-12
> -1.923480383365620E-07
> 2.716099808316560E-03
> -3.101037739059700E+00
>
> The first multiplied by the x^3; the next by x^2, and so forth.
>
> For the new x of 33660, I get a value of 5.858087089649060E+01 or about
58.58
>
> However, one could also use Bradens TLEval formula which does those
> calculations for you:
>
> =TLeval(A6,"Sheet1",1,1,1)
>
> (where 33660 is in A6) and obtain the same result:
>
> 5.858087089649060E+01
>
> By the way, using LINEST, which apparently does not use as good an
algorithym
> as does the trendline function on the chart, one gets the slightly
different
> answer of:
>
> 5.858087089649120E+01
>
> The formula for the above is
>
> =SUMPRODUCT(A6^{3,2,1,0},LINEST(B1:B5,A1:A5^{1,2,3}))
>
> with your data in A1:B5 and your new 'x' in A6.
>
> Try David's formula again with the above in mind, and let me know if you
can
> get it working for you.
>
>
> --ron

[CLR]

Thanks Peter...........

Very impressive the way the box jumps up with "the answers", but not
something I can use to solve my problem yet. The chart you popped up just
covered up my data and part of my chart I already had drawn. The formula in
your box was =5E-12x3-2E-07x2+0.0027x-3.101, whereby the one my chart puts
up in the Text Box is 3E-12x3-1E-07x2+0.0019x-0.2823..........and your final
answer is 51+ and mine was 64+...........I don't understnad the differences,
so I sure couldn't explain them to my user.......the pop-up freezes
operations and goes away leaving no answer anywhere when OK is
pressed........

But I do appreciate your suggestion, and will study your code more tomorrow
when I'm not so sleepy.........maybe I can get out of it what I
need.........

Thanks again,
Vaya con Dios,
Chuck, CABGx3

"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> Hi Chuck,
>
> Another one, just for fun.
>
> Sub Test()
>
> ''''''''''''''''''
> Dim ch As ChartObject
> On Error Resume Next
> Set ch = ActiveSheet.ChartObjects("TestChart") '.Chart
> On Error GoTo 0
> If ch Is Nothing Then
> With ActiveSheet.ChartObjects.Add(10, 10, 400, 200)
> .Chart.ChartType = xlXYScatter
> With .Chart.SeriesCollection.NewSeries
> .Formula = _
> "=SERIES(,{5610,11550,16830,22110,26600},{7,10,12,16,26},)"
> End With
> .Chart.ChartArea.Font.Size = 10
> .Name = "TestChart"
> .Select
> End With
> End If
>
> ''''''''''''''
>
> Dim sEqu As String, sFmla As String
> Dim A As Double, B As Double
>
> With ActiveChart.SeriesCollection(1).Trendlines.Add
> .Type = xlPolynomial
> .Order = 3
> .DisplayEquation = True
> sEqu = .DataLabel.Text
> 'maybe uncomment the Delete's first run
> .DataLabel.Delete
> .Delete
> End With
>
> A = 33660
> With Application
> sFmla = .Substitute(sEqu, "y = ", "")
> sFmla = .Substitute(sFmla, "x3", "*" & A & "^3")
> sFmla = .Substitute(sFmla, "x2", "*" & A & "^2")
> sFmla = .Substitute(sFmla, "x", "*" & A)
> End With
> B = Evaluate(sFmla)
> 'Debug.Print sEqu, A; B
> MsgBox sEqu & vbCr & "A " & A & vbCr & "B " & B
>
> End Sub
>
> Regards,
> Peter T
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Ron.............I have that from David already, but with my
limited
> > ability, could not figure out how to use it..........(I recognized the
> > word-wrap thing and attempted to correct it)..........but I still don't
> > know how to use the functions.
> >
> > Besides, it appears to be doing the math rather than just obtaining the
> TEXT
> > version of the formula from the Text Box, which is what I am trying to
> > do.......I can get it by selecting the box with the mouse and then
> > highlighting the formula, then Control-C, but that step does not
"record"
> on
> > a macro and I don't know how to code it.
> >
> > Here's the raw data my user is Charting, and looking to find the "B"
value
> > for an "A" of 33660.
> > The 3rd Order Poly Trendline gives a text box with this formula......(y
=
> > 5E-12x3 - 2E-07x2 + 0.0027x - 3.101)..........this is the one I'm trying
> to
> > extract from the box.
> >
> > A B
> > 5610 7
> > 11550 10
> > 16830 12
> > 22110 16
> > 26600 26
> > 33660 ?
> >
> >
> > Thanks again for your time..........
> > Vaya con Dios,
> > Chuck, CABGx3
> >
>
>

[Ron Rosenfeld]

On Sun, 6 Feb 2005 21:13:37 -0500, "CLR" <[email protected]> wrote:

>Thanks Ron..............
>
>All I get tonight is "Compile Errors", and #VALUE! in the cells for both
>functions........I guess I'll have to work on it a bit more tomorrow when
>I'm not so sleepy...........
>

I sent you a copy of the worksheet I used. Perhaps you can examine it and it
will help you understand what's going on.


--ron

[CLR]

Thanks Ron...........

I really appreciate that "extra mile" you went there by sending me a copy of
your sample workbook.......I looked and it's very similar to mine, "except"
that the formula is considerably different.........my chart puts up the
formuls as = 3E-12x3 - 1E-07x2 + 0.0019x - 0.2823, whereas yours is much
different......(I can't copy and paste it out of the picture you
sent)..........I just can't understand why these different methods come up
with significantly different answers to the same problem, (discounting
precision)............

Thanks again,
Vaya con Dios,
Chuck, CABGx3




"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Sun, 6 Feb 2005 21:13:37 -0500, "CLR" <[email protected]> wrote:
>
> >Thanks Ron..............
> >
> >All I get tonight is "Compile Errors", and #VALUE! in the cells for both
> >functions........I guess I'll have to work on it a bit more tomorrow when
> >I'm not so sleepy...........
> >
>
> I sent you a copy of the worksheet I used. Perhaps you can examine it and
it
> will help you understand what's going on.
>
>
> --ron

[Ron Rosenfeld]

On Sun, 6 Feb 2005 22:51:38 -0500, "CLR" <[email protected]> wrote:

>Thanks Ron...........
>
>I really appreciate that "extra mile" you went there by sending me a copy of
>your sample workbook.......I looked and it's very similar to mine, "except"
>that the formula is considerably different.........my chart puts up the
>formuls as = 3E-12x3 - 1E-07x2 + 0.0019x - 0.2823, whereas yours is much
>different......(I can't copy and paste it out of the picture you
>sent)..........I just can't understand why these different methods come up
>with significantly different answers to the same problem, (discounting
>precision)............
>
>Thanks again,
>Vaya con Dios,
>Chuck, CABGx3


Chuck,

You are getting considerably different numbers in your chart. Are you using
the same data you posted earlier, and creating an XY scatter chart? (Compare
the data I am using in A1:B5).

One problem: you obviously did not format the numbers in the data label to
Scientific with 14 or 15 decimal places (right click on that area; then select
format data label). Since you are extracting text, you must have it formatted
correctly first.




--ron

[Peter T]

Hi Chuck,

The idea of getting the formula from the Datalabel without precision is
totally flawed, more later. But first your comments:

> The formula in your box was
> y = 5E-12x3 - 2E-07x2 +0.0027x - 3.101
> whereby the one my chart puts up in the Text Box is
> y = 3E-12x3 - 1E-07x2 + 0.0019x- 0.2823
> .....I don't understnad the differences

You must be changing the goal posts! In your earlier message you said:

> > The 3rd Order Poly Trendline gives a text box
> > with this formula......
> > y = 5E-12x3 - 2E-07x2 + 0.0027x - 3.101

Ie, my formula returns the exact same formula you were expecting.

> the pop-up freezes operations and goes away leaving no
> answer anywhere when OK is pressed........

The "answers" are the remaining variables "sEqu", "sFmla" and "B". The
routine was just for illustration. Usuage depends on your requirements. An
example with the following assumptions:

- You have already created a chart
- it is a chartobject on a worksheet
- data of interest is in Series 1
- the chart is activated (selected)
- value A (say 33660) is in cell A20
- you want formula and result in cell B20

Sub Test2()
Dim sEqu As String, sFmla As String
Dim A As Double, B As Double, sAddr As String
Dim cht As Chart, x, y, sr As Series, i

Set cht = ActiveChart
If cht Is Nothing Then
MsgBox "Select chart": Exit Sub
End If

With ActiveChart.SeriesCollection(1).Trendlines.Add
.Type = xlPolynomial
.Order = 3
.DisplayEquation = True
sEqu = .DataLabel.Text
'maybe comment the Delete's subsequent runs
' .DataLabel.Delete
' .Delete
End With

A = 33660
sAddr = "A20"
With Application
'sFmla = .Substitute(sEqu, "y = ", "")
sFmla = .Substitute(sEqu, "y ", "")
sFmla = .Substitute(sFmla, "x3", "*" & sAddr & "^3")
sFmla = .Substitute(sFmla, "x2", "*" & sAddr & "^2")
sFmla = .Substitute(sFmla, "x", "*" & sAddr)
End With

sFmla = Trim(sFmla)

'put say 33660 in A20
Range("B20").Formula = sFmla
End Sub


Like I said, it returns the formula but it's is no good. Using LINEST I get
following with your original data :

x^3 0.00000000000493 vs 5E-12
x^2 -0.0000001923 vs -2E-07x2
x 0.002716 vs 0.0027
const -3.101014 vs -3.101

=LINEST(yValues, xValues^{1,2,3}) array entered into a row of 4 cells

which for an X of 33660 computes to a Y of 58.46 vs 51.86 !

Conclusion: why bother with getting formula off the chart when you can just
use LINEST. Or, take a much closer look at Ron's and David Braden's comments
concerning precision.

Regards,
Peter T

"CLR" <[email protected]> wrote in message
news:[email protected]...
> Thanks Peter...........
>
> Very impressive the way the box jumps up with "the answers", but not
> something I can use to solve my problem yet. The chart you popped up just
> covered up my data and part of my chart I already had drawn. The formula
in
> your box was =5E-12x3-2E-07x2+0.0027x-3.101, whereby the one my chart puts
> up in the Text Box is 3E-12x3-1E-07x2+0.0019x-0.2823..........and your
final
> answer is 51+ and mine was 64+...........I don't understnad the
differences,
> so I sure couldn't explain them to my user.......the pop-up freezes
> operations and goes away leaving no answer anywhere when OK is
> pressed........
>
> But I do appreciate your suggestion, and will study your code more
tomorrow
> when I'm not so sleepy.........maybe I can get out of it what I
> need.........
>
> Thanks again,
> Vaya con Dios,
> Chuck, CABGx3
>
> "Peter T" <peter_t@discussions> wrote in message
> news:[email protected]...
> > Hi Chuck,
> >
> > Another one, just for fun.
> >
> > Sub Test()
> >
> > ''''''''''''''''''
> > Dim ch As ChartObject
> > On Error Resume Next
> > Set ch = ActiveSheet.ChartObjects("TestChart") '.Chart
> > On Error GoTo 0
> > If ch Is Nothing Then
> > With ActiveSheet.ChartObjects.Add(10, 10, 400, 200)
> > .Chart.ChartType = xlXYScatter
> > With .Chart.SeriesCollection.NewSeries
> > .Formula = _
> > "=SERIES(,{5610,11550,16830,22110,26600},{7,10,12,16,26},)"
> > End With
> > .Chart.ChartArea.Font.Size = 10
> > .Name = "TestChart"
> > .Select
> > End With
> > End If
> >
> > ''''''''''''''
> >
> > Dim sEqu As String, sFmla As String
> > Dim A As Double, B As Double
> >
> > With ActiveChart.SeriesCollection(1).Trendlines.Add
> > .Type = xlPolynomial
> > .Order = 3
> > .DisplayEquation = True
> > sEqu = .DataLabel.Text
> > 'maybe uncomment the Delete's first run
> > .DataLabel.Delete
> > .Delete
> > End With
> >
> > A = 33660
> > With Application
> > sFmla = .Substitute(sEqu, "y = ", "")
> > sFmla = .Substitute(sFmla, "x3", "*" & A & "^3")
> > sFmla = .Substitute(sFmla, "x2", "*" & A & "^2")
> > sFmla = .Substitute(sFmla, "x", "*" & A)
> > End With
> > B = Evaluate(sFmla)
> > 'Debug.Print sEqu, A; B
> > MsgBox sEqu & vbCr & "A " & A & vbCr & "B " & B
> >
> > End Sub
> >
> > Regards,
> > Peter T
> >
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks Ron.............I have that from David already, but with my
> limited
> > > ability, could not figure out how to use it..........(I recognized the
> > > word-wrap thing and attempted to correct it)..........but I still
don't
> > > know how to use the functions.
> > >
> > > Besides, it appears to be doing the math rather than just obtaining
the
> > TEXT
> > > version of the formula from the Text Box, which is what I am trying to
> > > do.......I can get it by selecting the box with the mouse and then
> > > highlighting the formula, then Control-C, but that step does not
> "record"
> > on
> > > a macro and I don't know how to code it.
> > >
> > > Here's the raw data my user is Charting, and looking to find the "B"
> value
> > > for an "A" of 33660.
> > > The 3rd Order Poly Trendline gives a text box with this
formula......(y
> =
> > > 5E-12x3 - 2E-07x2 + 0.0027x - 3.101)..........this is the one I'm
trying
> > to
> > > extract from the box.
> > >
> > > A B
> > > 5610 7
> > > 11550 10
> > > 16830 12
> > > 22110 16
> > > 26600 26
> > > 33660 ?
> > >
> > >
> > > Thanks again for your time..........
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> >
> >
>
>

[Tom Ogilvy]

This simplified version should give you what you asked for in the variable
sFormula
Assumes one embedded chart on a sheet with a single series, a trendline
applied and the formula being displayed on the chart.

Sub GetFormula()
Dim cht as Chart
Dim ser as Series
Dim tline as Trendline
Dim sFormula as String

set cht = activesheet.ChartObjects(1).Chart
For Each ser In cht.SeriesCollection
If ser.Trendlines.Count = 1 Then
Set tLine = ser.Trendlines(1)
If tLine.DisplayEquation Then
sFormula = tLine.DataLabel.Text '<== this gets the formula
msgbox "Formula is: " & sFormula

end if
end if
Next

End Sub

--
Regards,
Tom Ogilvy



"CLR" <[email protected]> wrote in message
news:[email protected]...
> Hi Tom..........
>
> Thanks, but I must have done something wrong.............all I got by
> running it was the first coefficient in N6 and the third coefficient in
> N7.........no complete formula anywhere..........I'll give it a better
look
> tomorrow, as it's nearing my bedtime and my thinking-cap is starting to
> slip......
>
> Thanks again,
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:#[email protected]...
> > Here is one I wrote for someone about a year ago.
> > I marked the line that gets the formula. It goes on to break the
> > coefficents out and place them in separate cells starting in N6.
> >
> > Sub GetFormula()
> > Dim sStr As String, sStr1 As String
> > Dim sFormula As String, j As Long
> > Dim i As Long
> > Dim ser As Series, sChar As String
> > Dim tLine As Trendline
> > Dim cht As Chart
> > Dim rng As Range
> > Dim varr()
> > ReDim varr(1 To 10)
> > Set cht = ActiveSheet.ChartObjects(1).Chart
> > For Each ser In cht.SeriesCollection
> > If ser.Trendlines.Count = 1 Then
> > Set tLine = ser.Trendlines(1)
> > If tLine.DisplayEquation Then
> > sFormula = tLine.DataLabel.Text '<== this gets the formula
> > sFormula = Application.Substitute(sFormula, _
> > "y = ", "")
> > sFormula = Application.Substitute(sFormula, _
> > " + ", ",")
> > 'Debug.Print sFormula
> > j = 1
> > For i = 1 To Len(sFormula)
> > sChar = Mid(sFormula, i, 1)
> > If sChar = "," Or i = Len(sFormula) Then
> > If i = Len(sFormula) Then
> > sStr1 = sStr1 & sChar
> > End If
> > varr(j) = sStr1
> > sStr1 = ""
> > j = j + 1
> > Else
> > sStr1 = sStr1 & sChar
> > End If
> > Next
> > ReDim Preserve varr(1 To j - 1)
> > Set rng = Range("N6")
> > j = 1
> > For i = LBound(varr) To UBound(varr)
> > rng(j).Value = Val(varr(i))
> > j = j + 1
> > Next i
> > Exit Sub
> > End If
> > End If
> > Next
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks Ron.............I have that from David already, but with my
> limited
> > > ability, could not figure out how to use it..........(I recognized the
> > > word-wrap thing and attempted to correct it)..........but I still
don't
> > > know how to use the functions.
> > >
> > > Besides, it appears to be doing the math rather than just obtaining
the
> > TEXT
> > > version of the formula from the Text Box, which is what I am trying to
> > > do.......I can get it by selecting the box with the mouse and then
> > > highlighting the formula, then Control-C, but that step does not
> "record"
> > on
> > > a macro and I don't know how to code it.
> > >
> > > Here's the raw data my user is Charting, and looking to find the "B"
> value
> > > for an "A" of 33660.
> > > The 3rd Order Poly Trendline gives a text box with this
formula......(y
> =
> > > 5E-12x3 - 2E-07x2 + 0.0027x - 3.101)..........this is the one I'm
trying
> > to
> > > extract from the box.
> > >
> > > A B
> > > 5610 7
> > > 11550 10
> > > 16830 12
> > > 22110 16
> > > 26600 26
> > > 33660 ?
> > >
> > >
> > > Thanks again for your time..........
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > > "Ron Rosenfeld" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > On Sun, 6 Feb 2005 12:05:07 -0500, "CLR" <[email protected]>
> > wrote:
> > > >
> > > > >If someone would please be so kind..........I am in need of code to
> > > extract
> > > > >the formula from the Text Box that is put there when one creates an
> XY
> > > > >Scatter chart and adds a Third-order Polynomial Trendline.
> > > >
> > > > David Braden did this a few years ago. The code is below. Read the
> > notes
> > > > carefully and be sure to note that the values extracted will have
the
> > same
> > > > precision as the values displayed on the chart. So you will
probably
> > want
> > > to
> > > > set the format to a high precision, as he suggests.
> > > >
> > > > =============================================
> > > > Option Explicit
> > > > 'As J.W. Lewis has noted, Excel's Chart Trendline function yields
> > > > 'exceptionally good values for the models it fits. In contrast to
> > > > 'Excel's overall stats-capability, Trendline is a standout.
> > > > '
> > > > 'These functions provide a quasi-dynamic link to a chart's
*displayed*
> > > > 'trendline to help avoid deficencies of Excel's LINEST.
> > > > '
> > > > 'Function TLcoef(...) returns Trendline coefficients
> > > > 'Function TLeval(x, ...) evaluates the current trendline at a given
x
> > > > '
> > > > 'To specify the arguments of TLcoef, and the last 4 of TLeval:
> > > > ' vSheet is the name/number of the sheet containing the chart.
> > > > ' I strongly recommend you use the text name appearing in the
> Sheet
> > 's
> > > tab
> > > > ' 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
> > > > ' the standard toolbar.
> > > > ' If there is only one chart in the sheet, you can safely use
just
> 1
> > > as an
> > > > ' argument.
> > > > ' VSeries is a series name/number, and vTL is the series' trendline
> > > number.
> > > > ' Ideally you will have named the series, and refer to it by
> name.
> > > > ' To determine its 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.
> > > > '
> > > > 'David J. Braden maintains this as an open-community effort. Plz
post
> > or
> > > send
> > > > ' suggestions to him.
> > > >
> > > > 'First draft written 2003 March 1 by D J Braden
> > > >
> > > > 'Current concern(s)
> > > > ' (1) Because this is a function, we can't reliably get the
> underlying
> > > > Trendline
> > > > ' coefficients to greater accuracy than what is 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)
> > > > ' (2) Even though the functions are volatile, you may have to do a
> > > Worksheet
> > > > ' recalc to get things updated properly for anything changing the
> > chart
> > > to
> > > > ' get passed through to these functions. (
> > > >
> > > > '********************************************************
> > > >
> > > > Const cFirstNumPos = 5 ' pos. of first integer in displayed eqn
> > > > Const cMaxFormat = "0.00000000000000E+00"
> > > >
> > > > Function TLcoef(vSheet, vCht, vSeries, vTL)
> > > >
> > > > 'Return coefficients of an Excel chart trendline, *to precision
> > displayed*
> > > > '
> > > > 'Note: While Trendline seemingly always reports subsequent terms
from
> > > > 'a given one on, sometimes it reduces the order of the fit. So this
> > > function
> > > > 'returns, for a poly-fit, an array of length 1 + the order of the
> > > requested
> > > > fit,
> > > > ' *not* the number of values displayed. The last value in the
return
> > > 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 GoTo HanErr
> > > > Set o = Sheets(vSheet).ChartObjects(vCht).Chart. _
> > > > SeriesCollection(vSeries).Trendlines(vTL)
> > > > TLcoef = ExtractCoef(o, cFirstNumPos)
> > > > Exit Function
> > > >
> > > > HanErr:
> > > > TLcoef = CVErr(xlErrValue)
> > > > 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 Not CheckNum(vX, TLeval) Then Exit Function
> > > > If ParamErr(TLeval, vSheet, vCht, vSeries, vTL) Then Exit
Function
> > > >
> > > > Set o =
> > > Sheets(vSheet).ChartObjects(vCht).Chart.SeriesCollection(vSeries) _
> > > > .Trendlines(vTL)
> > > >
> > > > vRet = ExtractCoef(o, cFirstNumPos)
> > > > Select Case o.Type
> > > > Case xlLinear
> > > > vRet(1) = vX * vRet(1) + vRet(2)
> > > > Case xlExponential 'see comment above
> > > > vRet(1) = Exp(Log(vRet(1)) + vX * vRet(2))
> > > > Case xlLogarithmic
> > > > vRet(1) = vRet(1) * Log(vX) + vRet(2)
> > > > Case xlPower 'see comment above
> > > > vRet(1) = Exp(Log(vRet(1)) + Log(vX) * vRet(2))
> > > > Case xlPolynomial
> > > > Dim l As Long
> > > > vRet(1) = vRet(1) * vX + vRet(2)
> > > > For l = 3 To UBound(vRet)
> > > > vRet(1) = vX * vRet(1) + vRet(l)
> > > > Next
> > > > End Select
> > > > TLeval = vRet(1)
> > > > Exit Function
> > > >
> > > > HanErr:
> > > > TLeval = CVErr(xlErrValue)
> > > > End Function
> > > >
> > > > Private Function ExtractCoef(o As Trendline, ByVal lLastPos As Long)
> > > > Dim lCurPos As Long, s As String
> > > >
> > > > s = o.DataLabel.Text
> > > >
> > > > If o.DisplayRSquared Then
> > > > lCurPos = InStr(s, "R")
> > > > s = Left$(s, lCurPos - 1)
> > > > End If
> > > >
> > > > If o.Type <> xlPolynomial Then
> > > > ReDim v(1 To 2) As Double
> > > >
> > > > If o.Type = xlExponential Then
> > > > s = Application.WorksheetFunction.Substitute(s, "x", "")
> > > > s = Application.WorksheetFunction.Substitute(s, "e", "x")
> > > > ElseIf o.Type = xlLogarithmic Then
> > > > s = Application.WorksheetFunction.Substitute(s, "Ln(x)",
"x")
> > > > End If
> > > >
> > > > lCurPos = InStr(1, s, "x")
> > > > If lCurPos = 0 Then
> > > > v(2) = Mid(s, lLastPos)
> > > > Else
> > > > v(1) = Mid(s, lLastPos, lCurPos - lLastPos)
> > > > v(2) = Mid(s, lCurPos + 1)
> > > > End If
> > > >
> > > > Else 'have a polynomial
> > > > Dim lOrd As Long
> > > > ReDim v(1 To o.Order + 1) As Double
> > > >
> > > > lCurPos = InStr(s, "x")
> > > > If lCurPos = 0 Then
> > > > v(o.Order + 1) = Mid(s, lLastPos)
> > > > Exit Function 'with single constant term
> > > > End If
> > > > 'else
> > > > lOrd = Mid(s, lCurPos + 1, 1)
> > > > Do While lOrd > 1
> > > > v(UBound(v) - lOrd) = Mid(s, lLastPos, lCurPos - lLastPos)
> > > > lLastPos = lCurPos + 2
> > > > lCurPos = InStr(lLastPos, s, "x")
> > > > lOrd = lOrd - 1
> > > > Loop
> > > > 'peel off coeffs. for affine terms in eqn
> > > > v(o.Order) = Mid(s, lLastPos, lCurPos - lLastPos)
> > > > v(o.Order + 1) = Mid(s, lCurPos + 1)
> > > > End If
> > > > ExtractCoef = v
> > > > 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
> > > > End Function
> > > > =====================================
> > > >
> > > > --ron
> > >
> > >
> >
> >
>
>

[CLR]

Hi Peter......

You are of course right on all counts, as was Ron......this was an entirely
new arena to me and I had to go on what my user specifically asked of me, and
my gut feel to give me a warm fuzzy feeling as to what I was
doing..........as it turned out, my user really did want the higher precision
answer and I finally got the LINEST thing working and those results were
exactly what he was wanting........so, his need is fulfilled. As for your
macro2, I love it, just as I did your first one, and I will no doubt spend
much time dissecting them both to add to my VBA education.......

Thanks again for your time and understanding in all of this.....

Vaya con Dios,
Chuck, CABGx3


"Peter T" wrote:

> Hi Chuck,
>
> The idea of getting the formula from the Datalabel without precision is
> totally flawed, more later. But first your comments:
>
> > The formula in your box was
> > y = 5E-12x3 - 2E-07x2 +0.0027x - 3.101
> > whereby the one my chart puts up in the Text Box is
> > y = 3E-12x3 - 1E-07x2 + 0.0019x- 0.2823
> > .....I don't understnad the differences
>
> You must be changing the goal posts! In your earlier message you said:
>
> > > The 3rd Order Poly Trendline gives a text box
> > > with this formula......
> > > y = 5E-12x3 - 2E-07x2 + 0.0027x - 3.101
>
> Ie, my formula returns the exact same formula you were expecting.
>
> > the pop-up freezes operations and goes away leaving no
> > answer anywhere when OK is pressed........
>
> The "answers" are the remaining variables "sEqu", "sFmla" and "B". The
> routine was just for illustration. Usuage depends on your requirements. An
> example with the following assumptions:
>
> - You have already created a chart
> - it is a chartobject on a worksheet
> - data of interest is in Series 1
> - the chart is activated (selected)
> - value A (say 33660) is in cell A20
> - you want formula and result in cell B20
>
> Sub Test2()
> Dim sEqu As String, sFmla As String
> Dim A As Double, B As Double, sAddr As String
> Dim cht As Chart, x, y, sr As Series, i
>
> Set cht = ActiveChart
> If cht Is Nothing Then
> MsgBox "Select chart": Exit Sub
> End If
>
> With ActiveChart.SeriesCollection(1).Trendlines.Add
> .Type = xlPolynomial
> .Order = 3
> .DisplayEquation = True
> sEqu = .DataLabel.Text
> 'maybe comment the Delete's subsequent runs
> ' .DataLabel.Delete
> ' .Delete
> End With
>
> A = 33660
> sAddr = "A20"
> With Application
> 'sFmla = .Substitute(sEqu, "y = ", "")
> sFmla = .Substitute(sEqu, "y ", "")
> sFmla = .Substitute(sFmla, "x3", "*" & sAddr & "^3")
> sFmla = .Substitute(sFmla, "x2", "*" & sAddr & "^2")
> sFmla = .Substitute(sFmla, "x", "*" & sAddr)
> End With
>
> sFmla = Trim(sFmla)
>
> 'put say 33660 in A20
> Range("B20").Formula = sFmla
> End Sub
>
>
> Like I said, it returns the formula but it's is no good. Using LINEST I get
> following with your original data :
>
> x^3 0.00000000000493 vs 5E-12
> x^2 -0.0000001923 vs -2E-07x2
> x 0.002716 vs 0.0027
> const -3.101014 vs -3.101
>
> =LINEST(yValues, xValues^{1,2,3}) array entered into a row of 4 cells
>
> which for an X of 33660 computes to a Y of 58.46 vs 51.86 !
>
> Conclusion: why bother with getting formula off the chart when you can just
> use LINEST. Or, take a much closer look at Ron's and David Braden's comments
> concerning precision.
>
> Regards,
> Peter T
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Peter...........
> >
> > Very impressive the way the box jumps up with "the answers", but not
> > something I can use to solve my problem yet. The chart you popped up just
> > covered up my data and part of my chart I already had drawn. The formula
> in
> > your box was =5E-12x3-2E-07x2+0.0027x-3.101, whereby the one my chart puts
> > up in the Text Box is 3E-12x3-1E-07x2+0.0019x-0.2823..........and your
> final
> > answer is 51+ and mine was 64+...........I don't understnad the
> differences,
> > so I sure couldn't explain them to my user.......the pop-up freezes
> > operations and goes away leaving no answer anywhere when OK is
> > pressed........
> >
> > But I do appreciate your suggestion, and will study your code more
> tomorrow
> > when I'm not so sleepy.........maybe I can get out of it what I
> > need.........
> >
> > Thanks again,
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:[email protected]...
> > > Hi Chuck,
> > >
> > > Another one, just for fun.
> > >
> > > Sub Test()
> > >
> > > ''''''''''''''''''
> > > Dim ch As ChartObject
> > > On Error Resume Next
> > > Set ch = ActiveSheet.ChartObjects("TestChart") '.Chart
> > > On Error GoTo 0
> > > If ch Is Nothing Then
> > > With ActiveSheet.ChartObjects.Add(10, 10, 400, 200)
> > > .Chart.ChartType = xlXYScatter
> > > With .Chart.SeriesCollection.NewSeries
> > > .Formula = _
> > > "=SERIES(,{5610,11550,16830,22110,26600},{7,10,12,16,26},)"
> > > End With
> > > .Chart.ChartArea.Font.Size = 10
> > > .Name = "TestChart"
> > > .Select
> > > End With
> > > End If
> > >
> > > ''''''''''''''
> > >
> > > Dim sEqu As String, sFmla As String
> > > Dim A As Double, B As Double
> > >
> > > With ActiveChart.SeriesCollection(1).Trendlines.Add
> > > .Type = xlPolynomial
> > > .Order = 3
> > > .DisplayEquation = True
> > > sEqu = .DataLabel.Text
> > > 'maybe uncomment the Delete's first run
> > > .DataLabel.Delete
> > > .Delete
> > > End With
> > >
> > > A = 33660
> > > With Application
> > > sFmla = .Substitute(sEqu, "y = ", "")
> > > sFmla = .Substitute(sFmla, "x3", "*" & A & "^3")
> > > sFmla = .Substitute(sFmla, "x2", "*" & A & "^2")
> > > sFmla = .Substitute(sFmla, "x", "*" & A)
> > > End With
> > > B = Evaluate(sFmla)
> > > 'Debug.Print sEqu, A; B
> > > MsgBox sEqu & vbCr & "A " & A & vbCr & "B " & B
> > >
> > > End Sub
> > >
> > > Regards,
> > > Peter T
> > >
> > > "CLR" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Thanks Ron.............I have that from David already, but with my
> > limited
> > > > ability, could not figure out how to use it..........(I recognized the
> > > > word-wrap thing and attempted to correct it)..........but I still
> don't
> > > > know how to use the functions.
> > > >
> > > > Besides, it appears to be doing the math rather than just obtaining
> the
> > > TEXT
> > > > version of the formula from the Text Box, which is what I am trying to
> > > > do.......I can get it by selecting the box with the mouse and then
> > > > highlighting the formula, then Control-C, but that step does not
> > "record"
> > > on
> > > > a macro and I don't know how to code it.
> > > >
> > > > Here's the raw data my user is Charting, and looking to find the "B"
> > value
> > > > for an "A" of 33660.
> > > > The 3rd Order Poly Trendline gives a text box with this
> formula......(y
> > =
> > > > 5E-12x3 - 2E-07x2 + 0.0027x - 3.101)..........this is the one I'm
> trying
> > > to
> > > > extract from the box.
> > > >
> > > > A B
> > > > 5610 7
> > > > 11550 10
> > > > 16830 12
> > > > 22110 16
> > > > 26600 26
> > > > 33660 ?
> > > >
> > > >
> > > > Thanks again for your time..........
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > >
> > >
> >
> >
>
>
>

[CLR]

Hi Ron.......
You're right , of course, as you guys usually are.......it just takes me
awhile sometimes to get to the point where I recognize it.....<g>

I talked with my user this morning and he related that he actually did want
the right answer and not just the formula in the text box like he told me
originally......so, I finally got the LINEST thing working, and the numbers
it produced was what he was actually looking for.......so, as long as he is
satisfied, the story has a happy ending......

I really appreciate you hanging in there with me to the end of this thing...

Vaya con Dios,
Chuck, CABGx3



"Ron Rosenfeld" wrote:

> On Sun, 6 Feb 2005 22:51:38 -0500, "CLR" <[email protected]> wrote:
>
> >Thanks Ron...........
> >
> >I really appreciate that "extra mile" you went there by sending me a copy of
> >your sample workbook.......I looked and it's very similar to mine, "except"
> >that the formula is considerably different.........my chart puts up the
> >formuls as = 3E-12x3 - 1E-07x2 + 0.0019x - 0.2823, whereas yours is much
> >different......(I can't copy and paste it out of the picture you
> >sent)..........I just can't understand why these different methods come up
> >with significantly different answers to the same problem, (discounting
> >precision)............
> >
> >Thanks again,
> >Vaya con Dios,
> >Chuck, CABGx3
>
>
> Chuck,
>
> You are getting considerably different numbers in your chart. Are you using
> the same data you posted earlier, and creating an XY scatter chart? (Compare
> the data I am using in A1:B5).
>
> One problem: you obviously did not format the numbers in the data label to
> Scientific with 14 or 15 decimal places (right click on that area; then select
> format data label). Since you are extracting text, you must have it formatted
> correctly first.
>
>
>
>
> --ron
>

[Tom Ogilvy]

Regardless of the fact that you are using LINEST.
If you note in the comment in the Code Jerry Lewis (Phd, Statistics) is
quoted by David Braden (Phd, Statistics related) stating that Linest isn't
as good at formulating the formula for the trendline as the code that builds
the formula in the trendline itself. So while Linest will probably do the
job, this code will put the formula you need in the cell. It does use
maximum precision - not just the precision as displayed.

As written, select the cell for which you want to make a forecast. (the
cell with a ? in your example). and run the code. It will deposit a
formula referencing the cell to the left as the source for the value of X.
It will also use maximum precision. (this isn't as complex or as flexible as
Dave's code nor does it require breaking the string up as Peter T's code
does. it is much more flexible than Peter's, handling missing orders and
higher/lower order ).

Sub GetFormula1()
Dim sFormula As String
Dim ser As Series
Dim tLine As Trendline
Dim cht As Chart, sNum As String
Set cht = ActiveSheet.ChartObjects(1).Chart
Set ser = cht.SeriesCollection(1)
If ser.Trendlines.Count = 1 Then
Set tLine = ser.Trendlines(1)
If tLine.DisplayEquation Then
sNum = tLine.DataLabel.NumberFormat
tLine.DataLabel.NumberFormat = "0.00000000000000E+00"
sFormula = tLine.DataLabel.Text
tLine.DataLabel.NumberFormat = sNum
sFormula = Application.Substitute(sFormula, _
"y = ", "")
sFormula = Application.Substitute(sFormula, _
"x", "*" & ActiveCell.Offset(0, -1).Address(0, 0) & "^")
sFormula = Application.Substitute(sFormula, _
"^ ", " ")
ActiveCell.Formula = "=" & sFormula
End If
End If
End Sub

--
Regards,
Tom Ogilvy

"CLR" <[email protected]> wrote in message
news:[email protected]...
> Hi Ron.......
> You're right , of course, as you guys usually are.......it just takes me
> awhile sometimes to get to the point where I recognize it.....<g>
>
> I talked with my user this morning and he related that he actually did
want
> the right answer and not just the formula in the text box like he told me
> originally......so, I finally got the LINEST thing working, and the
numbers
> it produced was what he was actually looking for.......so, as long as he
is
> satisfied, the story has a happy ending......
>
> I really appreciate you hanging in there with me to the end of this
thing...
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Ron Rosenfeld" wrote:
>
> > On Sun, 6 Feb 2005 22:51:38 -0500, "CLR" <[email protected]>
wrote:
> >
> > >Thanks Ron...........
> > >
> > >I really appreciate that "extra mile" you went there by sending me a
copy of
> > >your sample workbook.......I looked and it's very similar to mine,
"except"
> > >that the formula is considerably different.........my chart puts up the
> > >formuls as = 3E-12x3 - 1E-07x2 + 0.0019x - 0.2823, whereas yours is
much
> > >different......(I can't copy and paste it out of the picture you
> > >sent)..........I just can't understand why these different methods come
up
> > >with significantly different answers to the same problem, (discounting
> > >precision)............
> > >
> > >Thanks again,
> > >Vaya con Dios,
> > >Chuck, CABGx3
> >
> >
> > Chuck,
> >
> > You are getting considerably different numbers in your chart. Are you
using
> > the same data you posted earlier, and creating an XY scatter chart?
(Compare
> > the data I am using in A1:B5).
> >
> > One problem: you obviously did not format the numbers in the data label
to
> > Scientific with 14 or 15 decimal places (right click on that area; then
select
> > format data label). Since you are extracting text, you must have it
formatted
> > correctly first.
> >
> >
> >
> >
> > --ron
> >

[CLR]

Hi Tom.......

Beautiful.......your macro does exactly what I asked.......Thank you very
much, kind Sir........

Unfortunately my user changed his rules and he now wants as his result, what
he sees from LINEST......so he's happy........but I do appreciate knowing how
to accomplish what I originally asked for in this thread, and you've given it
to me nicely.....it was really bugging me......

Thanks again Tom,
Vaya con Dios,
Chuck, CABGx3




"Tom Ogilvy" wrote:

> This simplified version should give you what you asked for in the variable
> sFormula
> Assumes one embedded chart on a sheet with a single series, a trendline
> applied and the formula being displayed on the chart.
>
> Sub GetFormula()
> Dim cht as Chart
> Dim ser as Series
> Dim tline as Trendline
> Dim sFormula as String
>
> set cht = activesheet.ChartObjects(1).Chart
> For Each ser In cht.SeriesCollection
> If ser.Trendlines.Count = 1 Then
> Set tLine = ser.Trendlines(1)
> If tLine.DisplayEquation Then
> sFormula = tLine.DataLabel.Text '<== this gets the formula
> msgbox "Formula is: " & sFormula
>
> end if
> end if
> Next
>
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Tom..........
> >
> > Thanks, but I must have done something wrong.............all I got by
> > running it was the first coefficient in N6 and the third coefficient in
> > N7.........no complete formula anywhere..........I'll give it a better
> look
> > tomorrow, as it's nearing my bedtime and my thinking-cap is starting to
> > slip......
> >
> > Thanks again,
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:#[email protected]...
> > > Here is one I wrote for someone about a year ago.
> > > I marked the line that gets the formula. It goes on to break the
> > > coefficents out and place them in separate cells starting in N6.
> > >
> > > Sub GetFormula()
> > > Dim sStr As String, sStr1 As String
> > > Dim sFormula As String, j As Long
> > > Dim i As Long
> > > Dim ser As Series, sChar As String
> > > Dim tLine As Trendline
> > > Dim cht As Chart
> > > Dim rng As Range
> > > Dim varr()
> > > ReDim varr(1 To 10)
> > > Set cht = ActiveSheet.ChartObjects(1).Chart
> > > For Each ser In cht.SeriesCollection
> > > If ser.Trendlines.Count = 1 Then
> > > Set tLine = ser.Trendlines(1)
> > > If tLine.DisplayEquation Then
> > > sFormula = tLine.DataLabel.Text '<== this gets the formula
> > > sFormula = Application.Substitute(sFormula, _
> > > "y = ", "")
> > > sFormula = Application.Substitute(sFormula, _
> > > " + ", ",")
> > > 'Debug.Print sFormula
> > > j = 1
> > > For i = 1 To Len(sFormula)
> > > sChar = Mid(sFormula, i, 1)
> > > If sChar = "," Or i = Len(sFormula) Then
> > > If i = Len(sFormula) Then
> > > sStr1 = sStr1 & sChar
> > > End If
> > > varr(j) = sStr1
> > > sStr1 = ""
> > > j = j + 1
> > > Else
> > > sStr1 = sStr1 & sChar
> > > End If
> > > Next
> > > ReDim Preserve varr(1 To j - 1)
> > > Set rng = Range("N6")
> > > j = 1
> > > For i = LBound(varr) To UBound(varr)
> > > rng(j).Value = Val(varr(i))
> > > j = j + 1
> > > Next i
> > > Exit Sub
> > > End If
> > > End If
> > > Next
> > > End Sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "CLR" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Thanks Ron.............I have that from David already, but with my
> > limited
> > > > ability, could not figure out how to use it..........(I recognized the
> > > > word-wrap thing and attempted to correct it)..........but I still
> don't
> > > > know how to use the functions.
> > > >
> > > > Besides, it appears to be doing the math rather than just obtaining
> the
> > > TEXT
> > > > version of the formula from the Text Box, which is what I am trying to
> > > > do.......I can get it by selecting the box with the mouse and then
> > > > highlighting the formula, then Control-C, but that step does not
> > "record"
> > > on
> > > > a macro and I don't know how to code it.
> > > >
> > > > Here's the raw data my user is Charting, and looking to find the "B"
> > value
> > > > for an "A" of 33660.
> > > > The 3rd Order Poly Trendline gives a text box with this
> formula......(y
> > =
> > > > 5E-12x3 - 2E-07x2 + 0.0027x - 3.101)..........this is the one I'm
> trying
> > > to
> > > > extract from the box.
> > > >
> > > > A B
> > > > 5610 7
> > > > 11550 10
> > > > 16830 12
> > > > 22110 16
> > > > 26600 26
> > > > 33660 ?
> > > >
> > > >
> > > > Thanks again for your time..........
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > >
> > > > "Ron Rosenfeld" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > On Sun, 6 Feb 2005 12:05:07 -0500, "CLR" <[email protected]>
> > > wrote:
> > > > >
> > > > > >If someone would please be so kind..........I am in need of code to
> > > > extract
> > > > > >the formula from the Text Box that is put there when one creates an
> > XY
> > > > > >Scatter chart and adds a Third-order Polynomial Trendline.
> > > > >
> > > > > David Braden did this a few years ago. The code is below. Read the
> > > notes
> > > > > carefully and be sure to note that the values extracted will have
> the
> > > same
> > > > > precision as the values displayed on the chart. So you will
> probably
> > > want
> > > > to
> > > > > set the format to a high precision, as he suggests.
> > > > >
> > > > > =============================================
> > > > > Option Explicit
> > > > > 'As J.W. Lewis has noted, Excel's Chart Trendline function yields
> > > > > 'exceptionally good values for the models it fits. In contrast to
> > > > > 'Excel's overall stats-capability, Trendline is a standout.
> > > > > '
> > > > > 'These functions provide a quasi-dynamic link to a chart's
> *displayed*
> > > > > 'trendline to help avoid deficencies of Excel's LINEST.
> > > > > '
> > > > > 'Function TLcoef(...) returns Trendline coefficients
> > > > > 'Function TLeval(x, ...) evaluates the current trendline at a given
> x
> > > > > '
> > > > > 'To specify the arguments of TLcoef, and the last 4 of TLeval:
> > > > > ' vSheet is the name/number of the sheet containing the chart.
> > > > > ' I strongly recommend you use the text name appearing in the
> > Sheet
> > > 's
> > > > tab
> > > > > ' 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
> > > > > ' the standard toolbar.
> > > > > ' If there is only one chart in the sheet, you can safely use
> just
> > 1
> > > > as an
> > > > > ' argument.
> > > > > ' VSeries is a series name/number, and vTL is the series' trendline
> > > > number.
> > > > > ' Ideally you will have named the series, and refer to it by
> > name.
> > > > > ' To determine its 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.
> > > > > '
> > > > > 'David J. Braden maintains this as an open-community effort. Plz
> post
> > > or
> > > > send
> > > > > ' suggestions to him.
> > > > >
> > > > > 'First draft written 2003 March 1 by D J Braden
> > > > >
> > > > > 'Current concern(s)
> > > > > ' (1) Because this is a function, we can't reliably get the
> > underlying
> > > > > Trendline
> > > > > ' coefficients to greater accuracy than what is 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)
> > > > > ' (2) Even though the functions are volatile, you may have to do a
> > > > Worksheet
> > > > > ' recalc to get things updated properly for anything changing the
> > > chart
> > > > to
> > > > > ' get passed through to these functions. (
> > > > >
> > > > > '********************************************************
> > > > >
> > > > > Const cFirstNumPos = 5 ' pos. of first integer in displayed eqn
> > > > > Const cMaxFormat = "0.00000000000000E+00"
> > > > >
> > > > > Function TLcoef(vSheet, vCht, vSeries, vTL)
> > > > >
> > > > > 'Return coefficients of an Excel chart trendline, *to precision
> > > displayed*
> > > > > '
> > > > > 'Note: While Trendline seemingly always reports subsequent terms
> from
> > > > > 'a given one on, sometimes it reduces the order of the fit. So this
> > > > function
> > > > > 'returns, for a poly-fit, an array of length 1 + the order of the
> > > > requested
> > > > > fit,
> > > > > ' *not* the number of values displayed. The last value in the
> return
> > > > 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 GoTo HanErr
> > > > > Set o = Sheets(vSheet).ChartObjects(vCht).Chart. _
> > > > > SeriesCollection(vSeries).Trendlines(vTL)
> > > > > TLcoef = ExtractCoef(o, cFirstNumPos)
> > > > > Exit Function
> > > > >
> > > > > HanErr:
> > > > > TLcoef = CVErr(xlErrValue)
> > > > > 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 Not CheckNum(vX, TLeval) Then Exit Function
> > > > > If ParamErr(TLeval, vSheet, vCht, vSeries, vTL) Then Exit
> Function
> > > > >
> > > > > Set o =
> > > > Sheets(vSheet).ChartObjects(vCht).Chart.SeriesCollection(vSeries) _
> > > > > .Trendlines(vTL)
> > > > >
> > > > > vRet = ExtractCoef(o, cFirstNumPos)
> > > > > Select Case o.Type
> > > > > Case xlLinear
> > > > > vRet(1) = vX * vRet(1) + vRet(2)
> > > > > Case xlExponential 'see comment above
> > > > > vRet(1) = Exp(Log(vRet(1)) + vX * vRet(2))
> > > > > Case xlLogarithmic
> > > > > vRet(1) = vRet(1) * Log(vX) + vRet(2)
> > > > > Case xlPower 'see comment above
> > > > > vRet(1) = Exp(Log(vRet(1)) + Log(vX) * vRet(2))
> > > > > Case xlPolynomial
> > > > > Dim l As Long
> > > > > vRet(1) = vRet(1) * vX + vRet(2)
> > > > > For l = 3 To UBound(vRet)
> > > > > vRet(1) = vX * vRet(1) + vRet(l)
> > > > > Next
> > > > > End Select
> > > > > TLeval = vRet(1)
> > > > > Exit Function
> > > > >
> > > > > HanErr:
> > > > > TLeval = CVErr(xlErrValue)
> > > > > End Function
> > > > >
> > > > > Private Function ExtractCoef(o As Trendline, ByVal lLastPos As Long)
> > > > > Dim lCurPos As Long, s As String
> > > > >
> > > > > s = o.DataLabel.Text
> > > > >
> > > > > If o.DisplayRSquared Then
> > > > > lCurPos = InStr(s, "R")
> > > > > s = Left$(s, lCurPos - 1)
> > > > > End If
> > > > >
> > > > > If o.Type <> xlPolynomial Then
> > > > > ReDim v(1 To 2) As Double
> > > > >
> > > > > If o.Type = xlExponential Then
> > > > > s = Application.WorksheetFunction.Substitute(s, "x", "")
> > > > > s = Application.WorksheetFunction.Substitute(s, "e", "x")
> > > > > ElseIf o.Type = xlLogarithmic Then
> > > > > s = Application.WorksheetFunction.Substitute(s, "Ln(x)",
> "x")
> > > > > End If
> > > > >
> > > > > lCurPos = InStr(1, s, "x")
> > > > > If lCurPos = 0 Then
> > > > > v(2) = Mid(s, lLastPos)
> > > > > Else
> > > > > v(1) = Mid(s, lLastPos, lCurPos - lLastPos)
> > > > > v(2) = Mid(s, lCurPos + 1)
> > > > > End If
> > > > >
> > > > > Else 'have a polynomial
> > > > > Dim lOrd As Long
> > > > > ReDim v(1 To o.Order + 1) As Double
> > > > >
> > > > > lCurPos = InStr(s, "x")
> > > > > If lCurPos = 0 Then
> > > > > v(o.Order + 1) = Mid(s, lLastPos)
> > > > > Exit Function 'with single constant term
> > > > > End If
> > > > > 'else
> > > > > lOrd = Mid(s, lCurPos + 1, 1)
> > > > > Do While lOrd > 1
> > > > > v(UBound(v) - lOrd) = Mid(s, lLastPos, lCurPos - lLastPos)
> > > > > lLastPos = lCurPos + 2
> > > > > lCurPos = InStr(lLastPos, s, "x")
> > > > > lOrd = lOrd - 1
> > > > > Loop
> > > > > 'peel off coeffs. for affine terms in eqn
> > > > > v(o.Order) = Mid(s, lLastPos, lCurPos - lLastPos)
> > > > > v(o.Order + 1) = Mid(s, lCurPos + 1)
> > > > > End If
> > > > > ExtractCoef = v
> > > > > 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
> > > > > End Function
> > > > > =====================================
> > > > >
> > > > > --ron
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>

[Tom Ogilvy]

See later posting that does better than LINEST - see cautions in code posted
by Ron Rosenfeld
--
Regards,
Tom Ogilvy

"CLR" <[email protected]> wrote in message
news:[email protected]...
> Hi Tom.......
>
> Beautiful.......your macro does exactly what I asked.......Thank you very
> much, kind Sir........
>
> Unfortunately my user changed his rules and he now wants as his result,
what
> he sees from LINEST......so he's happy........but I do appreciate knowing
how
> to accomplish what I originally asked for in this thread, and you've given
it
> to me nicely.....it was really bugging me......
>
> Thanks again Tom,
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
>
> "Tom Ogilvy" wrote:
>
> > This simplified version should give you what you asked for in the
variable
> > sFormula
> > Assumes one embedded chart on a sheet with a single series, a trendline
> > applied and the formula being displayed on the chart.
> >
> > Sub GetFormula()
> > Dim cht as Chart
> > Dim ser as Series
> > Dim tline as Trendline
> > Dim sFormula as String
> >
> > set cht = activesheet.ChartObjects(1).Chart
> > For Each ser In cht.SeriesCollection
> > If ser.Trendlines.Count = 1 Then
> > Set tLine = ser.Trendlines(1)
> > If tLine.DisplayEquation Then
> > sFormula = tLine.DataLabel.Text '<== this gets the formula
> > msgbox "Formula is: " & sFormula
> >
> > end if
> > end if
> > Next
> >
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Tom..........
> > >
> > > Thanks, but I must have done something wrong.............all I got by
> > > running it was the first coefficient in N6 and the third coefficient
in
> > > N7.........no complete formula anywhere..........I'll give it a better
> > look
> > > tomorrow, as it's nearing my bedtime and my thinking-cap is starting
to
> > > slip......
> > >
> > > Thanks again,
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > > "Tom Ogilvy" <[email protected]> wrote in message
> > > news:#[email protected]...
> > > > Here is one I wrote for someone about a year ago.
> > > > I marked the line that gets the formula. It goes on to break the
> > > > coefficents out and place them in separate cells starting in N6.
> > > >
> > > > Sub GetFormula()
> > > > Dim sStr As String, sStr1 As String
> > > > Dim sFormula As String, j As Long
> > > > Dim i As Long
> > > > Dim ser As Series, sChar As String
> > > > Dim tLine As Trendline
> > > > Dim cht As Chart
> > > > Dim rng As Range
> > > > Dim varr()
> > > > ReDim varr(1 To 10)
> > > > Set cht = ActiveSheet.ChartObjects(1).Chart
> > > > For Each ser In cht.SeriesCollection
> > > > If ser.Trendlines.Count = 1 Then
> > > > Set tLine = ser.Trendlines(1)
> > > > If tLine.DisplayEquation Then
> > > > sFormula = tLine.DataLabel.Text '<== this gets the formula
> > > > sFormula = Application.Substitute(sFormula, _
> > > > "y = ", "")
> > > > sFormula = Application.Substitute(sFormula, _
> > > > " + ", ",")
> > > > 'Debug.Print sFormula
> > > > j = 1
> > > > For i = 1 To Len(sFormula)
> > > > sChar = Mid(sFormula, i, 1)
> > > > If sChar = "," Or i = Len(sFormula) Then
> > > > If i = Len(sFormula) Then
> > > > sStr1 = sStr1 & sChar
> > > > End If
> > > > varr(j) = sStr1
> > > > sStr1 = ""
> > > > j = j + 1
> > > > Else
> > > > sStr1 = sStr1 & sChar
> > > > End If
> > > > Next
> > > > ReDim Preserve varr(1 To j - 1)
> > > > Set rng = Range("N6")
> > > > j = 1
> > > > For i = LBound(varr) To UBound(varr)
> > > > rng(j).Value = Val(varr(i))
> > > > j = j + 1
> > > > Next i
> > > > Exit Sub
> > > > End If
> > > > End If
> > > > Next
> > > > End Sub
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "CLR" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Thanks Ron.............I have that from David already, but with my
> > > limited
> > > > > ability, could not figure out how to use it..........(I recognized
the
> > > > > word-wrap thing and attempted to correct it)..........but I still
> > don't
> > > > > know how to use the functions.
> > > > >
> > > > > Besides, it appears to be doing the math rather than just
obtaining
> > the
> > > > TEXT
> > > > > version of the formula from the Text Box, which is what I am
trying to
> > > > > do.......I can get it by selecting the box with the mouse and then
> > > > > highlighting the formula, then Control-C, but that step does not
> > > "record"
> > > > on
> > > > > a macro and I don't know how to code it.
> > > > >
> > > > > Here's the raw data my user is Charting, and looking to find the
"B"
> > > value
> > > > > for an "A" of 33660.
> > > > > The 3rd Order Poly Trendline gives a text box with this
> > formula......(y
> > > =
> > > > > 5E-12x3 - 2E-07x2 + 0.0027x - 3.101)..........this is the one I'm
> > trying
> > > > to
> > > > > extract from the box.
> > > > >
> > > > > A B
> > > > > 5610 7
> > > > > 11550 10
> > > > > 16830 12
> > > > > 22110 16
> > > > > 26600 26
> > > > > 33660 ?
> > > > >
> > > > >
> > > > > Thanks again for your time..........
> > > > > Vaya con Dios,
> > > > > Chuck, CABGx3
> > > > >
> > > > >
> > > > > "Ron Rosenfeld" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > On Sun, 6 Feb 2005 12:05:07 -0500, "CLR"
<[email protected]>
> > > > wrote:
> > > > > >
> > > > > > >If someone would please be so kind..........I am in need of
code to
> > > > > extract
> > > > > > >the formula from the Text Box that is put there when one
creates an
> > > XY
> > > > > > >Scatter chart and adds a Third-order Polynomial Trendline.
> > > > > >
> > > > > > David Braden did this a few years ago. The code is below. Read
the
> > > > notes
> > > > > > carefully and be sure to note that the values extracted will
have
> > the
> > > > same
> > > > > > precision as the values displayed on the chart. So you will
> > probably
> > > > want
> > > > > to
> > > > > > set the format to a high precision, as he suggests.
> > > > > >
> > > > > > =============================================
> > > > > > Option Explicit
> > > > > > 'As J.W. Lewis has noted, Excel's Chart Trendline function
yields
> > > > > > 'exceptionally good values for the models it fits. In contrast
to
> > > > > > 'Excel's overall stats-capability, Trendline is a standout.
> > > > > > '
> > > > > > 'These functions provide a quasi-dynamic link to a chart's
> > *displayed*
> > > > > > 'trendline to help avoid deficencies of Excel's LINEST.
> > > > > > '
> > > > > > 'Function TLcoef(...) returns Trendline coefficients
> > > > > > 'Function TLeval(x, ...) evaluates the current trendline at a
given
> > x
> > > > > > '
> > > > > > 'To specify the arguments of TLcoef, and the last 4 of TLeval:
> > > > > > ' vSheet is the name/number of the sheet containing the chart.
> > > > > > ' I strongly recommend you use the text name appearing in
the
> > > Sheet
> > > > 's
> > > > > tab
> > > > > > ' 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
> > > > > > ' the standard toolbar.
> > > > > > ' If there is only one chart in the sheet, you can safely
use
> > just
> > > 1
> > > > > as an
> > > > > > ' argument.
> > > > > > ' VSeries is a series name/number, and vTL is the series'
trendline
> > > > > number.
> > > > > > ' Ideally you will have named the series, and refer to it
by
> > > name.
> > > > > > ' To determine its 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.
> > > > > > '
> > > > > > 'David J. Braden maintains this as an open-community effort.
Plz
> > post
> > > > or
> > > > > send
> > > > > > ' suggestions to him.
> > > > > >
> > > > > > 'First draft written 2003 March 1 by D J Braden
> > > > > >
> > > > > > 'Current concern(s)
> > > > > > ' (1) Because this is a function, we can't reliably get the
> > > underlying
> > > > > > Trendline
> > > > > > ' coefficients to greater accuracy than what is 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)
> > > > > > ' (2) Even though the functions are volatile, you may have to
do a
> > > > > Worksheet
> > > > > > ' recalc to get things updated properly for anything changing
the
> > > > chart
> > > > > to
> > > > > > ' get passed through to these functions. (
> > > > > >
> > > > > > '********************************************************
> > > > > >
> > > > > > Const cFirstNumPos = 5 ' pos. of first integer in displayed eqn
> > > > > > Const cMaxFormat = "0.00000000000000E+00"
> > > > > >
> > > > > > Function TLcoef(vSheet, vCht, vSeries, vTL)
> > > > > >
> > > > > > 'Return coefficients of an Excel chart trendline, *to precision
> > > > displayed*
> > > > > > '
> > > > > > 'Note: While Trendline seemingly always reports subsequent terms
> > from
> > > > > > 'a given one on, sometimes it reduces the order of the fit. So
this
> > > > > function
> > > > > > 'returns, for a poly-fit, an array of length 1 + the order of
the
> > > > > requested
> > > > > > fit,
> > > > > > ' *not* the number of values displayed. The last value in the
> > return
> > > > > 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 GoTo HanErr
> > > > > > Set o = Sheets(vSheet).ChartObjects(vCht).Chart. _
> > > > > > SeriesCollection(vSeries).Trendlines(vTL)
> > > > > > TLcoef = ExtractCoef(o, cFirstNumPos)
> > > > > > Exit Function
> > > > > >
> > > > > > HanErr:
> > > > > > TLcoef = CVErr(xlErrValue)
> > > > > > 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 Not CheckNum(vX, TLeval) Then Exit Function
> > > > > > If ParamErr(TLeval, vSheet, vCht, vSeries, vTL) Then Exit
> > Function
> > > > > >
> > > > > > Set o =
> > > > > Sheets(vSheet).ChartObjects(vCht).Chart.SeriesCollection(vSeries)
_
> > > > > > .Trendlines(vTL)
> > > > > >
> > > > > > vRet = ExtractCoef(o, cFirstNumPos)
> > > > > > Select Case o.Type
> > > > > > Case xlLinear
> > > > > > vRet(1) = vX * vRet(1) + vRet(2)
> > > > > > Case xlExponential 'see comment above
> > > > > > vRet(1) = Exp(Log(vRet(1)) + vX * vRet(2))
> > > > > > Case xlLogarithmic
> > > > > > vRet(1) = vRet(1) * Log(vX) + vRet(2)
> > > > > > Case xlPower 'see comment above
> > > > > > vRet(1) = Exp(Log(vRet(1)) + Log(vX) * vRet(2))
> > > > > > Case xlPolynomial
> > > > > > Dim l As Long
> > > > > > vRet(1) = vRet(1) * vX + vRet(2)
> > > > > > For l = 3 To UBound(vRet)
> > > > > > vRet(1) = vX * vRet(1) + vRet(l)
> > > > > > Next
> > > > > > End Select
> > > > > > TLeval = vRet(1)
> > > > > > Exit Function
> > > > > >
> > > > > > HanErr:
> > > > > > TLeval = CVErr(xlErrValue)
> > > > > > End Function
> > > > > >
> > > > > > Private Function ExtractCoef(o As Trendline, ByVal lLastPos As
Long)
> > > > > > Dim lCurPos As Long, s As String
> > > > > >
> > > > > > s = o.DataLabel.Text
> > > > > >
> > > > > > If o.DisplayRSquared Then
> > > > > > lCurPos = InStr(s, "R")
> > > > > > s = Left$(s, lCurPos - 1)
> > > > > > End If
> > > > > >
> > > > > > If o.Type <> xlPolynomial Then
> > > > > > ReDim v(1 To 2) As Double
> > > > > >
> > > > > > If o.Type = xlExponential Then
> > > > > > s = Application.WorksheetFunction.Substitute(s, "x",
"")
> > > > > > s = Application.WorksheetFunction.Substitute(s, "e",
"x")
> > > > > > ElseIf o.Type = xlLogarithmic Then
> > > > > > s = Application.WorksheetFunction.Substitute(s,
"Ln(x)",
> > "x")
> > > > > > End If
> > > > > >
> > > > > > lCurPos = InStr(1, s, "x")
> > > > > > If lCurPos = 0 Then
> > > > > > v(2) = Mid(s, lLastPos)
> > > > > > Else
> > > > > > v(1) = Mid(s, lLastPos, lCurPos - lLastPos)
> > > > > > v(2) = Mid(s, lCurPos + 1)
> > > > > > End If
> > > > > >
> > > > > > Else 'have a polynomial
> > > > > > Dim lOrd As Long
> > > > > > ReDim v(1 To o.Order + 1) As Double
> > > > > >
> > > > > > lCurPos = InStr(s, "x")
> > > > > > If lCurPos = 0 Then
> > > > > > v(o.Order + 1) = Mid(s, lLastPos)
> > > > > > Exit Function 'with single constant term
> > > > > > End If
> > > > > > 'else
> > > > > > lOrd = Mid(s, lCurPos + 1, 1)
> > > > > > Do While lOrd > 1
> > > > > > v(UBound(v) - lOrd) = Mid(s, lLastPos, lCurPos -
lLastPos)
> > > > > > lLastPos = lCurPos + 2
> > > > > > lCurPos = InStr(lLastPos, s, "x")
> > > > > > lOrd = lOrd - 1
> > > > > > Loop
> > > > > > 'peel off coeffs. for affine terms in eqn
> > > > > > v(o.Order) = Mid(s, lLastPos, lCurPos - lLastPos)
> > > > > > v(o.Order + 1) = Mid(s, lCurPos + 1)
> > > > > > End If
> > > > > > ExtractCoef = v
> > > > > > 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
> > > > > > End Function
> > > > > > =====================================
> > > > > >
> > > > > > --ron
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >

[CLR]

"GAD" Tom.........

That is really neat......."and more accurate to boot"?..........it don't get
much better than that.......

Thanks muchly,
Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

> Regardless of the fact that you are using LINEST.
> If you note in the comment in the Code Jerry Lewis (Phd, Statistics) is
> quoted by David Braden (Phd, Statistics related) stating that Linest isn't
> as good at formulating the formula for the trendline as the code that builds
> the formula in the trendline itself. So while Linest will probably do the
> job, this code will put the formula you need in the cell. It does use
> maximum precision - not just the precision as displayed.
>
> As written, select the cell for which you want to make a forecast. (the
> cell with a ? in your example). and run the code. It will deposit a
> formula referencing the cell to the left as the source for the value of X.
> It will also use maximum precision. (this isn't as complex or as flexible as
> Dave's code nor does it require breaking the string up as Peter T's code
> does. it is much more flexible than Peter's, handling missing orders and
> higher/lower order ).
>
> Sub GetFormula1()
> Dim sFormula As String
> Dim ser As Series
> Dim tLine As Trendline
> Dim cht As Chart, sNum As String
> Set cht = ActiveSheet.ChartObjects(1).Chart
> Set ser = cht.SeriesCollection(1)
> If ser.Trendlines.Count = 1 Then
> Set tLine = ser.Trendlines(1)
> If tLine.DisplayEquation Then
> sNum = tLine.DataLabel.NumberFormat
> tLine.DataLabel.NumberFormat = "0.00000000000000E+00"
> sFormula = tLine.DataLabel.Text
> tLine.DataLabel.NumberFormat = sNum
> sFormula = Application.Substitute(sFormula, _
> "y = ", "")
> sFormula = Application.Substitute(sFormula, _
> "x", "*" & ActiveCell.Offset(0, -1).Address(0, 0) & "^")
> sFormula = Application.Substitute(sFormula, _
> "^ ", " ")
> ActiveCell.Formula = "=" & sFormula
> End If
> End If
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Ron.......
> > You're right , of course, as you guys usually are.......it just takes me
> > awhile sometimes to get to the point where I recognize it.....<g>
> >
> > I talked with my user this morning and he related that he actually did
> want
> > the right answer and not just the formula in the text box like he told me
> > originally......so, I finally got the LINEST thing working, and the
> numbers
> > it produced was what he was actually looking for.......so, as long as he
> is
> > satisfied, the story has a happy ending......
> >
> > I really appreciate you hanging in there with me to the end of this
> thing...
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Ron Rosenfeld" wrote:
> >
> > > On Sun, 6 Feb 2005 22:51:38 -0500, "CLR" <[email protected]>
> wrote:
> > >
> > > >Thanks Ron...........
> > > >
> > > >I really appreciate that "extra mile" you went there by sending me a
> copy of
> > > >your sample workbook.......I looked and it's very similar to mine,
> "except"
> > > >that the formula is considerably different.........my chart puts up the
> > > >formuls as = 3E-12x3 - 1E-07x2 + 0.0019x - 0.2823, whereas yours is
> much
> > > >different......(I can't copy and paste it out of the picture you
> > > >sent)..........I just can't understand why these different methods come
> up
> > > >with significantly different answers to the same problem, (discounting
> > > >precision)............
> > > >
> > > >Thanks again,
> > > >Vaya con Dios,
> > > >Chuck, CABGx3
> > >
> > >
> > > Chuck,
> > >
> > > You are getting considerably different numbers in your chart. Are you
> using
> > > the same data you posted earlier, and creating an XY scatter chart?
> (Compare
> > > the data I am using in A1:B5).
> > >
> > > One problem: you obviously did not format the numbers in the data label
> to
> > > Scientific with 14 or 15 decimal places (right click on that area; then
> select
> > > format data label). Since you are extracting text, you must have it
> formatted
> > > correctly first.
> > >
> > >
> > >
> > >
> > > --ron
> > >
>
>
>

[Ron Rosenfeld]

On Mon, 7 Feb 2005 08:19:06 -0800, CLR <[email protected]> wrote:

>I talked with my user this morning and he related that he actually did want
>the right answer and not just the formula in the text box like he told me
>originally......so, I finally got the LINEST thing working, and the numbers
>it produced was what he was actually looking for.......so, as long as he is
>satisfied, the story has a happy ending......

Unfortunately, there are certain conditions in which LINEST will not give the
correct answer. The formula used in the TRENDLINE box is said to be more
"robust". I think that means it will give correct answers in situations where
LINEST gives an incorrect answer.

Since your user just wants the extrapolated answer, I would recommend using the
TLEval UDF from the code which I sent you. It uses the chart Trendline
formula, but only gives the answer, instead of the formula.


--ron

[Tom Ogilvy]

ActiveCell.Formula = "=" & sFormula
followed by
ActiveCell.Formula = ActiveCell.Value

will quiet that concern.

--
Regards,
Tom Ogilvy


"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 7 Feb 2005 08:19:06 -0800, CLR <[email protected]>
wrote:
>
> >I talked with my user this morning and he related that he actually did
want
> >the right answer and not just the formula in the text box like he told me
> >originally......so, I finally got the LINEST thing working, and the
numbers
> >it produced was what he was actually looking for.......so, as long as he
is
> >satisfied, the story has a happy ending......
>
> Unfortunately, there are certain conditions in which LINEST will not give
the
> correct answer. The formula used in the TRENDLINE box is said to be more
> "robust". I think that means it will give correct answers in situations
where
> LINEST gives an incorrect answer.
>
> Since your user just wants the extrapolated answer, I would recommend
using the
> TLEval UDF from the code which I sent you. It uses the chart Trendline
> formula, but only gives the answer, instead of the formula.
>
>
> --ron

[Peter T]

Hi Tom,

> it is much more flexible than Peter's, handling missing orders and
> higher/lower order ).

I don't quite follow "handling missing orders and higher/lower order". There
are some differences in implementation between our macros, but are they
fundamentally different ?

Small point, trying your macro in XL2K, while an worksheet chart is active
it fails trying to return the ActiveCell. Error 91

Just about to post under Ron's concerning points you've both made, would
also welcome your comments.

Regards,
Peter T


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Regardless of the fact that you are using LINEST.
> If you note in the comment in the Code Jerry Lewis (Phd, Statistics) is
> quoted by David Braden (Phd, Statistics related) stating that Linest isn't
> as good at formulating the formula for the trendline as the code that
builds
> the formula in the trendline itself. So while Linest will probably do the
> job, this code will put the formula you need in the cell. It does use
> maximum precision - not just the precision as displayed.
>
> As written, select the cell for which you want to make a forecast. (the
> cell with a ? in your example). and run the code. It will deposit a
> formula referencing the cell to the left as the source for the value of X.
> It will also use maximum precision. (this isn't as complex or as flexible
as
> Dave's code nor does it require breaking the string up as Peter T's code
> does. it is much more flexible than Peter's, handling missing orders and
> higher/lower order ).
>
> Sub GetFormula1()
> Dim sFormula As String
> Dim ser As Series
> Dim tLine As Trendline
> Dim cht As Chart, sNum As String
> Set cht = ActiveSheet.ChartObjects(1).Chart
> Set ser = cht.SeriesCollection(1)
> If ser.Trendlines.Count = 1 Then
> Set tLine = ser.Trendlines(1)
> If tLine.DisplayEquation Then
> sNum = tLine.DataLabel.NumberFormat
> tLine.DataLabel.NumberFormat = "0.00000000000000E+00"
> sFormula = tLine.DataLabel.Text
> tLine.DataLabel.NumberFormat = sNum
> sFormula = Application.Substitute(sFormula, _
> "y = ", "")
> sFormula = Application.Substitute(sFormula, _
> "x", "*" & ActiveCell.Offset(0, -1).Address(0, 0) & "^")
> sFormula = Application.Substitute(sFormula, _
> "^ ", " ")
> ActiveCell.Formula = "=" & sFormula
> End If
> End If
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Ron.......
> > You're right , of course, as you guys usually are.......it just takes me
> > awhile sometimes to get to the point where I recognize it.....<g>
> >
> > I talked with my user this morning and he related that he actually did
> want
> > the right answer and not just the formula in the text box like he told
me
> > originally......so, I finally got the LINEST thing working, and the
> numbers
> > it produced was what he was actually looking for.......so, as long as he
> is
> > satisfied, the story has a happy ending......
> >
> > I really appreciate you hanging in there with me to the end of this
> thing...
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Ron Rosenfeld" wrote:
> >
> > > On Sun, 6 Feb 2005 22:51:38 -0500, "CLR" <[email protected]>
> wrote:
> > >
> > > >Thanks Ron...........
> > > >
> > > >I really appreciate that "extra mile" you went there by sending me a
> copy of
> > > >your sample workbook.......I looked and it's very similar to mine,
> "except"
> > > >that the formula is considerably different.........my chart puts up
the
> > > >formuls as = 3E-12x3 - 1E-07x2 + 0.0019x - 0.2823, whereas yours is
> much
> > > >different......(I can't copy and paste it out of the picture you
> > > >sent)..........I just can't understand why these different methods
come
> up
> > > >with significantly different answers to the same problem,
(discounting
> > > >precision)............
> > > >
> > > >Thanks again,
> > > >Vaya con Dios,
> > > >Chuck, CABGx3
> > >
> > >
> > > Chuck,
> > >
> > > You are getting considerably different numbers in your chart. Are you
> using
> > > the same data you posted earlier, and creating an XY scatter chart?
> (Compare
> > > the data I am using in A1:B5).
> > >
> > > One problem: you obviously did not format the numbers in the data
label
> to
> > > Scientific with 14 or 15 decimal places (right click on that area;
then
> select
> > > format data label). Since you are extracting text, you must have it
> formatted
> > > correctly first.
> > >
> > >
> > >
> > >
> > > --ron
> > >
>
>

[Peter T]

Hi Ron,

> Unfortunately, there are certain conditions in which LINEST will not give
the
> correct answer. The formula used in the TRENDLINE box is said to be more
> "robust". I think that means it will give correct answers in situations
where
> LINEST gives an incorrect answer.

Tom mentioned similar and I also recall reading about this. However an
observation:

All the macros in this thread (mine with change of number format) return
exactly the same results as LINEST to 14dp, based on Chuck's original data
set. With this set, and all others I've tried that "make sense" with 3rd
order polynomial, suggests that the chart's polynomial trendline uses the
exact same calculation as LINEST.

Chuck's data:
=LINEST({7;10;12;16;26},{5610;11550;16830;22110;26600}^{1,2,3})
array entered in 4 cells in a row

Would you have an example of data where a polynomial trendline differs from
LINEST, as in "there are certain conditions ...."

Regards,
Peter T

[Peter T]

Hi Chuck,

With Ron's NumberFormat suggestion, if you insert into my previous macros as
follows:

..DisplayEquation = True
..DataLabel.NumberFormat = "0.000000000000000E+00"
sEqu = .DataLabel.Text

You should get the same accuracy and results as LINEST, and same as returned
in the other macros in this thread.

Regards,
Peter Thornton

"CLR" <[email protected]> wrote in message
news:[email protected]...
> Hi Peter......
>
> You are of course right on all counts, as was Ron......this was an
entirely
> new arena to me and I had to go on what my user specifically asked of me,
and
> my gut feel to give me a warm fuzzy feeling as to what I was
> doing..........as it turned out, my user really did want the higher
precision
> answer and I finally got the LINEST thing working and those results were
> exactly what he was wanting........so, his need is fulfilled. As for your
> macro2, I love it, just as I did your first one, and I will no doubt spend
> much time dissecting them both to add to my VBA education.......
>
> Thanks again for your time and understanding in all of this.....
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Peter T" wrote:
>
> > Hi Chuck,
> >
> > The idea of getting the formula from the Datalabel without precision is
> > totally flawed, more later. But first your comments:
> >
> > > The formula in your box was
> > > y = 5E-12x3 - 2E-07x2 +0.0027x - 3.101
> > > whereby the one my chart puts up in the Text Box is
> > > y = 3E-12x3 - 1E-07x2 + 0.0019x- 0.2823
> > > .....I don't understnad the differences
> >
> > You must be changing the goal posts! In your earlier message you said:
> >
> > > > The 3rd Order Poly Trendline gives a text box
> > > > with this formula......
> > > > y = 5E-12x3 - 2E-07x2 + 0.0027x - 3.101
> >
> > Ie, my formula returns the exact same formula you were expecting.
> >
> > > the pop-up freezes operations and goes away leaving no
> > > answer anywhere when OK is pressed........
> >
> > The "answers" are the remaining variables "sEqu", "sFmla" and "B". The
> > routine was just for illustration. Usuage depends on your requirements.
An
> > example with the following assumptions:
> >
> > - You have already created a chart
> > - it is a chartobject on a worksheet
> > - data of interest is in Series 1
> > - the chart is activated (selected)
> > - value A (say 33660) is in cell A20
> > - you want formula and result in cell B20
> >
> > Sub Test2()
> > Dim sEqu As String, sFmla As String
> > Dim A As Double, B As Double, sAddr As String
> > Dim cht As Chart, x, y, sr As Series, i
> >
> > Set cht = ActiveChart
> > If cht Is Nothing Then
> > MsgBox "Select chart": Exit Sub
> > End If
> >
> > With ActiveChart.SeriesCollection(1).Trendlines.Add
> > .Type = xlPolynomial
> > .Order = 3
> > .DisplayEquation = True
> > sEqu = .DataLabel.Text
> > 'maybe comment the Delete's subsequent runs
> > ' .DataLabel.Delete
> > ' .Delete
> > End With
> >
> > A = 33660
> > sAddr = "A20"
> > With Application
> > 'sFmla = .Substitute(sEqu, "y = ", "")
> > sFmla = .Substitute(sEqu, "y ", "")
> > sFmla = .Substitute(sFmla, "x3", "*" & sAddr & "^3")
> > sFmla = .Substitute(sFmla, "x2", "*" & sAddr & "^2")
> > sFmla = .Substitute(sFmla, "x", "*" & sAddr)
> > End With
> >
> > sFmla = Trim(sFmla)
> >
> > 'put say 33660 in A20
> > Range("B20").Formula = sFmla
> > End Sub
> >
> >
> > Like I said, it returns the formula but it's is no good. Using LINEST I
get
> > following with your original data :
> >
> > x^3 0.00000000000493 vs 5E-12
> > x^2 -0.0000001923 vs -2E-07x2
> > x 0.002716 vs 0.0027
> > const -3.101014 vs -3.101
> >
> > =LINEST(yValues, xValues^{1,2,3}) array entered into a row of 4 cells
> >
> > which for an X of 33660 computes to a Y of 58.46 vs 51.86 !
> >
> > Conclusion: why bother with getting formula off the chart when you can
just
> > use LINEST. Or, take a much closer look at Ron's and David Braden's
comments
> > concerning precision.
> >
> > Regards,
> > Peter T
> >
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks Peter...........
> > >
> > > Very impressive the way the box jumps up with "the answers", but not
> > > something I can use to solve my problem yet. The chart you popped up
just
> > > covered up my data and part of my chart I already had drawn. The
formula
> > in
> > > your box was =5E-12x3-2E-07x2+0.0027x-3.101, whereby the one my chart
puts
> > > up in the Text Box is 3E-12x3-1E-07x2+0.0019x-0.2823..........and your
> > final
> > > answer is 51+ and mine was 64+...........I don't understnad the
> > differences,
> > > so I sure couldn't explain them to my user.......the pop-up freezes
> > > operations and goes away leaving no answer anywhere when OK is
> > > pressed........
> > >
> > > But I do appreciate your suggestion, and will study your code more
> > tomorrow
> > > when I'm not so sleepy.........maybe I can get out of it what I
> > > need.........
> > >
> > > Thanks again,
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > > "Peter T" <peter_t@discussions> wrote in message
> > > news:[email protected]...
> > > > Hi Chuck,
> > > >
> > > > Another one, just for fun.
> > > >
> > > > Sub Test()
> > > >
> > > > ''''''''''''''''''
> > > > Dim ch As ChartObject
> > > > On Error Resume Next
> > > > Set ch = ActiveSheet.ChartObjects("TestChart") '.Chart
> > > > On Error GoTo 0
> > > > If ch Is Nothing Then
> > > > With ActiveSheet.ChartObjects.Add(10, 10, 400, 200)
> > > > .Chart.ChartType = xlXYScatter
> > > > With .Chart.SeriesCollection.NewSeries
> > > > .Formula = _
> > > > "=SERIES(,{5610,11550,16830,22110,26600},{7,10,12,16,26},)"
> > > > End With
> > > > .Chart.ChartArea.Font.Size = 10
> > > > .Name = "TestChart"
> > > > .Select
> > > > End With
> > > > End If
> > > >
> > > > ''''''''''''''
> > > >
> > > > Dim sEqu As String, sFmla As String
> > > > Dim A As Double, B As Double
> > > >
> > > > With ActiveChart.SeriesCollection(1).Trendlines.Add
> > > > .Type = xlPolynomial
> > > > .Order = 3
> > > > .DisplayEquation = True
> > > > sEqu = .DataLabel.Text
> > > > 'maybe uncomment the Delete's first run
> > > > .DataLabel.Delete
> > > > .Delete
> > > > End With
> > > >
> > > > A = 33660
> > > > With Application
> > > > sFmla = .Substitute(sEqu, "y = ", "")
> > > > sFmla = .Substitute(sFmla, "x3", "*" & A & "^3")
> > > > sFmla = .Substitute(sFmla, "x2", "*" & A & "^2")
> > > > sFmla = .Substitute(sFmla, "x", "*" & A)
> > > > End With
> > > > B = Evaluate(sFmla)
> > > > 'Debug.Print sEqu, A; B
> > > > MsgBox sEqu & vbCr & "A " & A & vbCr & "B " & B
> > > >
> > > > End Sub
> > > >
> > > > Regards,
> > > > Peter T
> > > >
> > > > "CLR" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Thanks Ron.............I have that from David already, but with my
> > > limited
> > > > > ability, could not figure out how to use it..........(I recognized
the
> > > > > word-wrap thing and attempted to correct it)..........but I still
> > don't
> > > > > know how to use the functions.
> > > > >
> > > > > Besides, it appears to be doing the math rather than just
obtaining
> > the
> > > > TEXT
> > > > > version of the formula from the Text Box, which is what I am
trying to
> > > > > do.......I can get it by selecting the box with the mouse and then
> > > > > highlighting the formula, then Control-C, but that step does not
> > > "record"
> > > > on
> > > > > a macro and I don't know how to code it.
> > > > >
> > > > > Here's the raw data my user is Charting, and looking to find the
"B"
> > > value
> > > > > for an "A" of 33660.
> > > > > The 3rd Order Poly Trendline gives a text box with this
> > formula......(y
> > > =
> > > > > 5E-12x3 - 2E-07x2 + 0.0027x - 3.101)..........this is the one I'm
> > trying
> > > > to
> > > > > extract from the box.
> > > > >
> > > > > A B
> > > > > 5610 7
> > > > > 11550 10
> > > > > 16830 12
> > > > > 22110 16
> > > > > 26600 26
> > > > > 33660 ?
> > > > >
> > > > >
> > > > > Thanks again for your time..........
> > > > > Vaya con Dios,
> > > > > Chuck, CABGx3
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >

[Tom Ogilvy]

> Small point, trying your macro in XL2K, while an worksheet chart is active
> it fails trying to return the ActiveCell. Error 91

Guess in your eagerness, you didn't read the directions. Of course I could
make it bullet proof, but that wasn't the point.

>As written, select the cell for which you want to make a forecast. (the
>cell with a ? in your example). and run the code.

But thanks for emphasizing again for the OP that the directions should be
followed.

You seem to see my comment as criticism. There was no criticism. You built
a focused solution to help CLR. That's great. I was just positioning the
code I offered as between the complexity/functionality of yours and David
Braden's.

I didn't go back and analyze your code line by line to write a critique so
perhaps I overstated the limitations. I was working from a previous reading
somewhat earlier. I was thinking it was a bit more restrictive. Your code
should handle lower order and missing terms fine, but not higher order
(.Order = 4 for example). Plus you build the trendline with fixed
attributes, rather than use that established by the user( less flexible).
You sent a new post just before this expanding the precision, so that wasn't
present when I posted.

No, the implementations are not conceptually different, but I would still
say that mine is more flexible (although I overstated that) and at the time
had higher precision. Those are the two things which I considered that
placed it between yours and David Braden's in my thinking. Sorry you seem
to think that is criticism - no criticism intened. It was meant to just help
the OP see why it was posted rather than duplicating what others had posted.

--
Regards,
Tom Ogilvy




"Peter T" <peter_t@discussions> wrote in message
news:%[email protected]...
> Hi Tom,
>
> > it is much more flexible than Peter's, handling missing orders and
> > higher/lower order ).
>
> I don't quite follow "handling missing orders and higher/lower order".
There
> are some differences in implementation between our macros, but are they
> fundamentally different ?
>
> Small point, trying your macro in XL2K, while an worksheet chart is active
> it fails trying to return the ActiveCell. Error 91
>
> Just about to post under Ron's concerning points you've both made, would
> also welcome your comments.
>
> Regards,
> Peter T
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Regardless of the fact that you are using LINEST.
> > If you note in the comment in the Code Jerry Lewis (Phd, Statistics) is
> > quoted by David Braden (Phd, Statistics related) stating that Linest
isn't
> > as good at formulating the formula for the trendline as the code that
> builds
> > the formula in the trendline itself. So while Linest will probably do
the
> > job, this code will put the formula you need in the cell. It does use
> > maximum precision - not just the precision as displayed.
> >
> > As written, select the cell for which you want to make a forecast. (the
> > cell with a ? in your example). and run the code. It will deposit a
> > formula referencing the cell to the left as the source for the value of
X.
> > It will also use maximum precision. (this isn't as complex or as
flexible
> as
> > Dave's code nor does it require breaking the string up as Peter T's code
> > does. it is much more flexible than Peter's, handling missing orders and
> > higher/lower order ).
> >
> > Sub GetFormula1()
> > Dim sFormula As String
> > Dim ser As Series
> > Dim tLine As Trendline
> > Dim cht As Chart, sNum As String
> > Set cht = ActiveSheet.ChartObjects(1).Chart
> > Set ser = cht.SeriesCollection(1)
> > If ser.Trendlines.Count = 1 Then
> > Set tLine = ser.Trendlines(1)
> > If tLine.DisplayEquation Then
> > sNum = tLine.DataLabel.NumberFormat
> > tLine.DataLabel.NumberFormat = "0.00000000000000E+00"
> > sFormula = tLine.DataLabel.Text
> > tLine.DataLabel.NumberFormat = sNum
> > sFormula = Application.Substitute(sFormula, _
> > "y = ", "")
> > sFormula = Application.Substitute(sFormula, _
> > "x", "*" & ActiveCell.Offset(0, -1).Address(0, 0) & "^")
> > sFormula = Application.Substitute(sFormula, _
> > "^ ", " ")
> > ActiveCell.Formula = "=" & sFormula
> > End If
> > End If
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Ron.......
> > > You're right , of course, as you guys usually are.......it just takes
me
> > > awhile sometimes to get to the point where I recognize it.....<g>
> > >
> > > I talked with my user this morning and he related that he actually did
> > want
> > > the right answer and not just the formula in the text box like he told
> me
> > > originally......so, I finally got the LINEST thing working, and the
> > numbers
> > > it produced was what he was actually looking for.......so, as long as
he
> > is
> > > satisfied, the story has a happy ending......
> > >
> > > I really appreciate you hanging in there with me to the end of this
> > thing...
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > >
> > > "Ron Rosenfeld" wrote:
> > >
> > > > On Sun, 6 Feb 2005 22:51:38 -0500, "CLR" <[email protected]>
> > wrote:
> > > >
> > > > >Thanks Ron...........
> > > > >
> > > > >I really appreciate that "extra mile" you went there by sending me
a
> > copy of
> > > > >your sample workbook.......I looked and it's very similar to mine,
> > "except"
> > > > >that the formula is considerably different.........my chart puts up
> the
> > > > >formuls as = 3E-12x3 - 1E-07x2 + 0.0019x - 0.2823, whereas yours
is
> > much
> > > > >different......(I can't copy and paste it out of the picture you
> > > > >sent)..........I just can't understand why these different methods
> come
> > up
> > > > >with significantly different answers to the same problem,
> (discounting
> > > > >precision)............
> > > > >
> > > > >Thanks again,
> > > > >Vaya con Dios,
> > > > >Chuck, CABGx3
> > > >
> > > >
> > > > Chuck,
> > > >
> > > > You are getting considerably different numbers in your chart. Are
you
> > using
> > > > the same data you posted earlier, and creating an XY scatter chart?
> > (Compare
> > > > the data I am using in A1:B5).
> > > >
> > > > One problem: you obviously did not format the numbers in the data
> label
> > to
> > > > Scientific with 14 or 15 decimal places (right click on that area;
> then
> > select
> > > > format data label). Since you are extracting text, you must have it
> > formatted
> > > > correctly first.
> > > >
> > > >
> > > >
> > > >
> > > > --ron
> > > >
> >
> >
>
>

[Tom Ogilvy]

from Microsoft KB article:
(http://support.microsoft.com/default...b;en-us;828533)
Microsoft has made extensive changes to the LINEST function to correct
incorrect formulas that are used when the regression line must go through
the origin. The changes also pay more attention to issues that involve
collinear predictor variables. Because of these extensive improvements, this
article focuses more on the improvements and less on instructing users about
how to use LINEST.

Jerry Lewis and David Braden had done a lot of research on the statisical
functions and I believe were major champions/driving forces for changes, som
of which have benn made in xl2003.

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> Hi Ron,
>
> > Unfortunately, there are certain conditions in which LINEST will not
give
> the
> > correct answer. The formula used in the TRENDLINE box is said to be
more
> > "robust". I think that means it will give correct answers in situations
> where
> > LINEST gives an incorrect answer.
>
> Tom mentioned similar and I also recall reading about this. However an
> observation:
>
> All the macros in this thread (mine with change of number format) return
> exactly the same results as LINEST to 14dp, based on Chuck's original data
> set. With this set, and all others I've tried that "make sense" with 3rd
> order polynomial, suggests that the chart's polynomial trendline uses the
> exact same calculation as LINEST.
>
> Chuck's data:
> =LINEST({7;10;12;16;26},{5610;11550;16830;22110;26600}^{1,2,3})
> array entered in 4 cells in a row
>
> Would you have an example of data where a polynomial trendline differs
from
> LINEST, as in "there are certain conditions ...."
>
> Regards,
> Peter T
>
>

[Peter T]

Hi Tom,

No, I really didn't take anything you said as criticism, my comments were
intended at face value. But had you wanted to I would not in the least mind,
quite the reverse - same goes for anything I may post in the future.

My main confusion concerned "missing orders and higher/lower order" which I
took to relate to polynomials, but now see referred to precision (lacking in
my original macro).

Thanks for the considered reply.

Regards,
Peter T

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> > Small point, trying your macro in XL2K, while an worksheet chart is
active
> > it fails trying to return the ActiveCell. Error 91
>
> Guess in your eagerness, you didn't read the directions. Of course I
could
> make it bullet proof, but that wasn't the point.
>
> >As written, select the cell for which you want to make a forecast. (the
> >cell with a ? in your example). and run the code.
>
> But thanks for emphasizing again for the OP that the directions should be
> followed.
>
> You seem to see my comment as criticism. There was no criticism. You
built
> a focused solution to help CLR. That's great. I was just positioning the
> code I offered as between the complexity/functionality of yours and David
> Braden's.
>
> I didn't go back and analyze your code line by line to write a critique so
> perhaps I overstated the limitations. I was working from a previous
reading
> somewhat earlier. I was thinking it was a bit more restrictive. Your code
> should handle lower order and missing terms fine, but not higher order
> (.Order = 4 for example). Plus you build the trendline with fixed
> attributes, rather than use that established by the user( less flexible).
> You sent a new post just before this expanding the precision, so that
wasn't
> present when I posted.
>
> No, the implementations are not conceptually different, but I would still
> say that mine is more flexible (although I overstated that) and at the
time
> had higher precision. Those are the two things which I considered that
> placed it between yours and David Braden's in my thinking. Sorry you seem
> to think that is criticism - no criticism intened. It was meant to just
help
> the OP see why it was posted rather than duplicating what others had
posted.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%[email protected]...
> > Hi Tom,
> >
> > > it is much more flexible than Peter's, handling missing orders and
> > > higher/lower order ).
> >
> > I don't quite follow "handling missing orders and higher/lower order".
> There
> > are some differences in implementation between our macros, but are they
> > fundamentally different ?
> >
> > Small point, trying your macro in XL2K, while an worksheet chart is
active
> > it fails trying to return the ActiveCell. Error 91
> >
> > Just about to post under Ron's concerning points you've both made, would
> > also welcome your comments.
> >
> > Regards,
> > Peter T
> >
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:[email protected]...
> > > Regardless of the fact that you are using LINEST.
> > > If you note in the comment in the Code Jerry Lewis (Phd, Statistics)
is
> > > quoted by David Braden (Phd, Statistics related) stating that Linest
> isn't
> > > as good at formulating the formula for the trendline as the code that
> > builds
> > > the formula in the trendline itself. So while Linest will probably do
> the
> > > job, this code will put the formula you need in the cell. It does use
> > > maximum precision - not just the precision as displayed.
> > >
> > > As written, select the cell for which you want to make a forecast.
(the
> > > cell with a ? in your example). and run the code. It will deposit a
> > > formula referencing the cell to the left as the source for the value
of
> X.
> > > It will also use maximum precision. (this isn't as complex or as
> flexible
> > as
> > > Dave's code nor does it require breaking the string up as Peter T's
code
> > > does. it is much more flexible than Peter's, handling missing orders
and
> > > higher/lower order ).
> > >
> > > Sub GetFormula1()
> > > Dim sFormula As String
> > > Dim ser As Series
> > > Dim tLine As Trendline
> > > Dim cht As Chart, sNum As String
> > > Set cht = ActiveSheet.ChartObjects(1).Chart
> > > Set ser = cht.SeriesCollection(1)
> > > If ser.Trendlines.Count = 1 Then
> > > Set tLine = ser.Trendlines(1)
> > > If tLine.DisplayEquation Then
> > > sNum = tLine.DataLabel.NumberFormat
> > > tLine.DataLabel.NumberFormat = "0.00000000000000E+00"
> > > sFormula = tLine.DataLabel.Text
> > > tLine.DataLabel.NumberFormat = sNum
> > > sFormula = Application.Substitute(sFormula, _
> > > "y = ", "")
> > > sFormula = Application.Substitute(sFormula, _
> > > "x", "*" & ActiveCell.Offset(0, -1).Address(0, 0) & "^")
> > > sFormula = Application.Substitute(sFormula, _
> > > "^ ", " ")
> > > ActiveCell.Formula = "=" & sFormula
> > > End If
> > > End If
> > > End Sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "CLR" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Ron.......
> > > > You're right , of course, as you guys usually are.......it just
takes
> me
> > > > awhile sometimes to get to the point where I recognize it.....<g>
> > > >
> > > > I talked with my user this morning and he related that he actually
did
> > > want
> > > > the right answer and not just the formula in the text box like he
told
> > me
> > > > originally......so, I finally got the LINEST thing working, and the
> > > numbers
> > > > it produced was what he was actually looking for.......so, as long
as
> he
> > > is
> > > > satisfied, the story has a happy ending......
> > > >
> > > > I really appreciate you hanging in there with me to the end of this
> > > thing...
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > >
> > > >
> > > > "Ron Rosenfeld" wrote:
> > > >
> > > > > On Sun, 6 Feb 2005 22:51:38 -0500, "CLR"
<[email protected]>
> > > wrote:
> > > > >
> > > > > >Thanks Ron...........
> > > > > >
> > > > > >I really appreciate that "extra mile" you went there by sending
me
> a
> > > copy of
> > > > > >your sample workbook.......I looked and it's very similar to
mine,
> > > "except"
> > > > > >that the formula is considerably different.........my chart puts
up
> > the
> > > > > >formuls as = 3E-12x3 - 1E-07x2 + 0.0019x - 0.2823, whereas yours
> is
> > > much
> > > > > >different......(I can't copy and paste it out of the picture you
> > > > > >sent)..........I just can't understand why these different
methods
> > come
> > > up
> > > > > >with significantly different answers to the same problem,
> > (discounting
> > > > > >precision)............
> > > > > >
> > > > > >Thanks again,
> > > > > >Vaya con Dios,
> > > > > >Chuck, CABGx3
> > > > >
> > > > >
> > > > > Chuck,
> > > > >
> > > > > You are getting considerably different numbers in your chart. Are
> you
> > > using
> > > > > the same data you posted earlier, and creating an XY scatter
chart?
> > > (Compare
> > > > > the data I am using in A1:B5).
> > > > >
> > > > > One problem: you obviously did not format the numbers in the data
> > label
> > > to
> > > > > Scientific with 14 or 15 decimal places (right click on that area;
> > then
> > > select
> > > > > format data label). Since you are extracting text, you must have
it
> > > formatted
> > > > > correctly first.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > --ron
> > > > >
> > >
> > >
> >
> >
>
>

[Ron Rosenfeld]

On Mon, 7 Feb 2005 21:32:43 -0000, "Peter T" <peter_t@discussions> wrote:

>Hi Ron,
>
>> Unfortunately, there are certain conditions in which LINEST will not give
>the
>> correct answer. The formula used in the TRENDLINE box is said to be more
>> "robust". I think that means it will give correct answers in situations
>where
>> LINEST gives an incorrect answer.
>
>Tom mentioned similar and I also recall reading about this. However an
>observation:
>
>All the macros in this thread (mine with change of number format) return
>exactly the same results as LINEST to 14dp, based on Chuck's original data
>set. With this set, and all others I've tried that "make sense" with 3rd
>order polynomial, suggests that the chart's polynomial trendline uses the
>exact same calculation as LINEST.
>
>Chuck's data:
>=LINEST({7;10;12;16;26},{5610;11550;16830;22110;26600}^{1,2,3})
>array entered in 4 cells in a row
>
>Would you have an example of data where a polynomial trendline differs from
>LINEST, as in "there are certain conditions ...."
>
>Regards,
>Peter T
>

Others more knowledgeable than I (Braden and Lewis and Harlan) have discussed
this extensively in the past, so I'm sure there is data available via a Google
search. Look for something like LINEST error in the newsgroup. The explanation
there will be much more detailed than any I could come up with.

For the particular example posted, the differences are minimal, and the
difference in computation of the new 'y' for the OP's new 'x' of 33660 is only
5.258016244624740E-13.

In Excel 2003, there was work done on the Excel statistical functions, with
improvement in LINEST (as well as other functions). For some reason, I thought
the OP was using XL97, but I may have him confused with someone else.


--ron

[CLR]

"Whew"............you guys left me in the dust a loooong time ago
<g>...........my problem is solved tho, my user is happy, and I really
really do appreciate all the time and effort you all have put in to help and
educate me...........without these newsgroups some of us would truly be
lost.

By the way, you were right again.........I am using XL97 at work where I
have this problem.

Thanks again to everyone...........
Vaya con Dios,
Chuck, CABGx3


"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 7 Feb 2005 21:32:43 -0000, "Peter T" <peter_t@discussions> wrote:
>
> >Hi Ron,
> >
> >> Unfortunately, there are certain conditions in which LINEST will not
give
> >the
> >> correct answer. The formula used in the TRENDLINE box is said to be
more
> >> "robust". I think that means it will give correct answers in
situations
> >where
> >> LINEST gives an incorrect answer.
> >
> >Tom mentioned similar and I also recall reading about this. However an
> >observation:
> >
> >All the macros in this thread (mine with change of number format) return
> >exactly the same results as LINEST to 14dp, based on Chuck's original
data
> >set. With this set, and all others I've tried that "make sense" with 3rd
> >order polynomial, suggests that the chart's polynomial trendline uses the
> >exact same calculation as LINEST.
> >
> >Chuck's data:
> >=LINEST({7;10;12;16;26},{5610;11550;16830;22110;26600}^{1,2,3})
> >array entered in 4 cells in a row
> >
> >Would you have an example of data where a polynomial trendline differs
from
> >LINEST, as in "there are certain conditions ...."
> >
> >Regards,
> >Peter T
> >
>
> Others more knowledgeable than I (Braden and Lewis and Harlan) have
discussed
> this extensively in the past, so I'm sure there is data available via a
Google
> search. Look for something like LINEST error in the newsgroup. The
explanation
> there will be much more detailed than any I could come up with.
>
> For the particular example posted, the differences are minimal, and the
> difference in computation of the new 'y' for the OP's new 'x' of 33660 is
only
> 5.258016244624740E-13.
>
> In Excel 2003, there was work done on the Excel statistical functions,
with
> improvement in LINEST (as well as other functions). For some reason, I
thought
> the OP was using XL97, but I may have him confused with someone else.
>
>
> --ron

[Peter T]

Tom - thanks for the link. Not exactly bed time reading <g> but I've tried
to get to grips with it.

Ron - thanks also for your followup. As you say there is some detail of
LINEST problems in this ng. Presumably all statisticians are fully aware I
but don't suppose it does any harm to have the issue raised from time to
time.

Chuck - did a quick test in XL97 and get same results as in XL2K. Don't
think you should have any problems with LINEST with the particular data set
you have.

Clearly though there may be some sets that error with pre XL2003 and, from
what I interpret, how the function is used. In some of these cases applying
common sense might indicate a problem, such as comparing calculated results
with what one might expect.

On which point neither you nor I did with the early results, eg test the
formula on known x's & y's. (which I did by prior to my second followup to
you).

Your client's original request to go with the simplified formula, at first
glance, seemed very reasonable. It gave coefficients down to very small
albeit rounded decimals. He probably only wanted a result to the nearest
whole number, even that as a rough projection. Might well have expected the
simplified formula to be good enough. But it gave a very wrong result - a
lesson!

Regards,
Peter T

[CLR]

A lesson indeed Peter........

Every time I come to these groups for an answer, I usually go away with more
than I was smart enough to ask for in the first place......and even extra
stuff that I can use on the next project. Obviously I know nothing about
statistics, in this instance I was only trying to help a Chemist friend with
his Excel charting problems.....(hence my insistance that I only wanted to
access the label formula) but I certainly learned a lot in the process.

Again, I want to thank you for all your time and efforts in my behalf.

Vaya con Dios,
Chuck, CABGx3


"Peter T" wrote:

> Tom - thanks for the link. Not exactly bed time reading <g> but I've tried
> to get to grips with it.
>
> Ron - thanks also for your followup. As you say there is some detail of
> LINEST problems in this ng. Presumably all statisticians are fully aware I
> but don't suppose it does any harm to have the issue raised from time to
> time.
>
> Chuck - did a quick test in XL97 and get same results as in XL2K. Don't
> think you should have any problems with LINEST with the particular data set
> you have.
>
> Clearly though there may be some sets that error with pre XL2003 and, from
> what I interpret, how the function is used. In some of these cases applying
> common sense might indicate a problem, such as comparing calculated results
> with what one might expect.
>
> On which point neither you nor I did with the early results, eg test the
> formula on known x's & y's. (which I did by prior to my second followup to
> you).
>
> Your client's original request to go with the simplified formula, at first
> glance, seemed very reasonable. It gave coefficients down to very small
> albeit rounded decimals. He probably only wanted a result to the nearest
> whole number, even that as a rough projection. Might well have expected the
> simplified formula to be good enough. But it gave a very wrong result - a
> lesson!
>
> Regards,
> Peter T
>
>
>