Can macro code to copy a trendline equation be written? 1

[NormPeterson]

What I want to do is utilize a polynomial curve fit equation (of specified formatting) from a chart. The relatively small amount of basic input data is not well-suited to using lookups and interpolations directly.

It's easy enough to manually copy this information from within the chart object and paste it into a target cell using menus and the mouse, but I'd like to get a subroutine to do this. Thus far, I can't seem to get any code learned from mouse manipulation or keystrokes to copy the contents of the trendline equation box to the clipboard or wherever. If it matters, I have Excel 97 SR-1 and Microsoft Windows 98 4.10.2222 A .

Downstream of the copy/paste procedure that I'm trying to automate, 'Right' and 'Mid' functions separate the individual coefficients, thus allowing the trendline equation that pastes as text to be rewritten as a useable cell formula.

Norm

 

[pmover]

Norm,

Please look at the following posting & responses in thread770-23296 regarding the linest function and its capabilities.

While i do use the trendline feature, I also use the linest function to determine the equation constants as well.

i hope this helps.
good luck!
-pmover

 

[NormPeterson]

Star for that. LINEST works quite well for my typical data, as it did with jproj's sample problem after I got the data typed in correctly (transcription errors being the sort of thing this whole effort is intended to help eliminate). Once it's written, I guess that having the x^0 coefficient "out of logical sequence" with the rest won't matter even though it's a bit odd to look at while developing.

Norm

 

[NormPeterson]

Just thought I'd add to this topic a method for coping with a variable number of input data points.

=LINEST(INDIRECT(O7),INDIRECT(N7)^{4,3,2,1},TRUE),

where N7 and O7 are the string formulae

="N9:N"&8+N6 and ="O9:O"&8+N6,

for the X and Y data in columns N and O respectively, where N6 is a COUNT of the number of input pairs entered in N9:Oxx. The data must be on consecutive rows (starting in row 9 in this instance) but it need not fill up the entire COUNT range of rows.

=COUNT(N9:Nxx)

Norm