transferring trendline polynomium coefficients to spreadsheet cells

[Artur]

I frequently use the trendline feature in Excel charts to adjust a
function, usually a polynomium, to the plotted points. Then, I
transfer the coefficients to cells on the spreadsheet where the chart
is embedded, so that I can use them in formulas that evaluate the
polynomium. I used to do this manually, by copying the equation text,
pasting it on several cells and editing each of them. A really tedious
process! Then, I developed a macro in Visual Basic to automate this
task. In most cases it works well, but there are some problems. For
instance, if one of the coefficients is exactly 1 Excel doesn't write
it, for instance, if the coefficient of the term of first degree is 1,
in the trendline datalabel Excel just puts x and not 1x. And if the
coefficient is exactly zero then the term to which it refers is simply
omitted, like in usual writing. This is a big complication to a macro
that deals with texts and extracts the coefficients from the trendline
equation box. Actually, it's usually much more difficult and
bothersome to develop macros that deals with texts than macros that
deals with mathematics.

I guess there must be a better solution to put the coefficients into
spreadsheets cells than to extract them from the trendline datalabel.
Of course, they are recorded somewhere in the spreadsheet and, maybe,
there's a way to access them and put them into VBA variables.

Any suggestion will be welcome!
Thank you very much
Artur

[Bernard Liengme]

Hi Artur,
You can use LINEST to get the polynomial coefficients into cells. See
http://www.stfx.ca/people/bliengme/ExcelTips

Best wishes
Bernard

[Jon Peltier]

The copy paste routine is rather inefficient to get the coefficients of the
trendline formula into the worksheet. I suggest you look into the LINEST
worksheet function, which returns the coefficients and if you desire, some
statistical information regarding these coefficients.

I didn't open your attachment (see
http://www.cpearson.com/excel/newposte.htm).

- Jon
_______

In article <888f01c20a8d$e54cd740$95e62ecf@tkmsftngxs02>, ra...@hal-pc.org
says...
>
>This is a multi-part message in MIME format.
>
>------=_NextPart_000_888C_01C20A53.38EDFF40
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>
>I would recommend you look
>at:http://lacher.com/toc/tutbusiness.htm
>The downloadable example under forecasting techniques
>includes a macro which may do a better job. I have a
>different problem and wonder if you'd have a suggestion. I
>have time related data which I chart (stock prices etc.
>which I import into Excel). The chart trendline formula
>seems to be totally useless when I enter the excel ser. #
>for the date into the formula to check it. I do the
>copy/paste routine you described in your note and I have
>verified the formula is correctly tranferred. I have
>attached an Excel 2000 file to illustrate my problem. Note
>that the calculated values can be used to determiine the
>correct results 1 data point behind. (see columns d and H)

>>.
>>