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