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).Data Label.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

On Sun, 6 Feb 2005 12:05:07 -0500, "CLR" 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

On Sun, 6 Feb 2005 13:57:35 -0500, "CLR" 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

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" wrote in message
...
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

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" wrote in message
...
On Sun, 6 Feb 2005 13:57:35 -0500, "CLR" 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

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
...
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" wrote in message
...
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

On Sun, 6 Feb 2005 21:13:37 -0500, "CLR" 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

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" wrote in message
...
On Sun, 6 Feb 2005 21:13:37 -0500, "CLR" 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

On Sun, 6 Feb 2005 22:51:38 -0500, "CLR" 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

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" wrote in message
...
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
...
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" wrote in message
...
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

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

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" wrote in message
...
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
...
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" wrote in message
...
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

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" wrote in message
...
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"
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

"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" wrote in message
...
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"
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

On Mon, 7 Feb 2005 08:19:06 -0800, CLR 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

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

will quiet that concern.

--
Regards,
Tom Ogilvy


"Ron Rosenfeld" wrote in message
...
On Mon, 7 Feb 2005 08:19:06 -0800, CLR
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

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" wrote in message
...
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" wrote in message
...
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
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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"
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

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;26 600}^{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

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
...
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" wrote in message
...
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" wrote in message
...
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"
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

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
...
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;26 600}^{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

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" wrote in message
...
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
...
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" wrote in message
...
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" wrote in message
...
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"

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

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;2 6600}^{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

"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" wrote in message
...
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;2 6600}^{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

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

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