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]

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]

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

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) _

 

[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

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]

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]

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

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

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]

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

 

[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

 

[Ron Rosenfeld]

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]

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:

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

 

[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

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

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


the
TEXT "record"

formula......(y

 

[Guest]

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

 

[Guest]

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

 

[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

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

 

[Guest]

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]

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