Extract Trendline Equation (2000 sr1)

Topic

New Reply

I have an XY scatterpoint graph with a logarithmic trendline. The trendline equation is displayed in this form:

y = a*ln(x) + b

How can I, using either functions or VBA, get the “a” and “b” constants into cells?

Thanks,
Ken

Reply | Quote

Replies

Say that your x values are in A1:A10, and your y values in B1:B10. In C1 (or in another free column), enter the formula

=LN(A1)

and fill down to C10. Select two empty cells next to each other, for example D1:E1. Enter the formula

=LINEST(B1:B10,C1:C10)

and confirm with Ctrl+Shift+Enter to make it an array formula. The first cell will contain the coefficient a and the second cell will contain the constant b. You can hide column C if you like.

Reply | Quote

Thanks Hans! Works Great.!

Reply | Quote

You could use, directly (again confirm with ctrl-shift-enter)

=LINEST(B1:B10,LN(A1:A10))

And not create the “intermediate column” at all.

Steve

Reply | Quote

Yep, thanks.

Reply | Quote