Trendline coefficient extraction code

[JosieB]

Hello

I recently found the code below posted in a thread and have been trying to make use of it as it does exactly what I needed to achieve.

The code works beautifully (I am using the TLcoef function) with just one issue. When I save, close and then return to the workbook, the error message '"#Err: No trendline matches the specified parameters" appears where the array should be, even though nothing has changed except for going out and in again. Also (strangely) I can no longer select any cells on the sheet.

Does anybody have any idea why this may be happening and what I can do about it?

Thanks in advance

Josie

(code in next thread)

[JosieB]

Please Login or Register  to view this content.

[shg]

Why not just use LINEST or LOGEST to get the trendline coefficients on the worksheet directly?

[JosieB]

Hi there

My graphs contain polynomial trendlines. My understanding is that LINEST can only provide coefficients for a linear relationship but not for a polynomial. Is that correct?

Thanks

Josie

[shg]

LINEST will do linear, polynomial, exponential, and power series.


{=LINEST(y, x^{1,2,3})} for example, returns the coefficients of a cubic polynomial.

[JosieB]

Fantastic, thanks. I didn't know that!

As it happens, I found another way around the problem, in case anybody is interested. I used a macro to call the function and assigned that macro to a command button rather than actually entering the function array in the cells and it works fine.

Please Login or Register  to view this content.

LINEST is probably neater though

Thanks!

[shg]

Good job. No need to select, though:

Please Login or Register  to view this content.