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