creating logarithmic trendline equation function

[mparker]

Hello,

I was just wondering how I can create a logarithmic trendline equatio
function out of three points. I don’t want to have to manually copy i
from a chart as there are over fifty different log equations. I woul
like it to just calculate a result automatically in a cell.

Example:

Three data points:

X
15.00 23.00 35.00

Y
25.34 24.38 22.72


Trendline equation created from three point in chart:
y = -3.0925Ln(x) + 33.836

Let x = 25

y = 23.88

I sure hope you can help me this.

Thanks,

Mitchel

mparke

 

[Tom Ogilvy]

assume you data

A1: 15 B1: 25.34
A2: 23 B2: 24.38
A3: 35 B3: 22.72

A5: =ln(a1)
A6: =ln(A2)
A7: =ln(A3)

select C11 array enter (ctrl+Shift+Enter rather than just enter)
=Linest(B1:B3,A5:A7)

c1: -3.089633383

D1: 33.82637732

Answer is slightly different, but we may not have exactly the same inputs.

 

[Guest]

Mitchel

You can use the LOGEST function

If you put your data in the range B13 as described, then array enter the formula =LOGEST(B22,B11) in cells B7:C7. The results will be (approx) .99 (B7) and 27.56 (C7). The formula is then =$C$7*$B$7^B1 copied across to cover the X cells B1 to B3

Ton

----- mparker wrote: ----


Hello

I was just wondering how I can create a logarithmic trendline equatio
function out of three points. I don’t want to have to manually copy i
from a chart as there are over fifty different log equations. I woul
like it to just calculate a result automatically in a cell

Example

Three data points

X
15.00 23.00 35.0


25.34 24.38 22.7


Trendline equation created from three point in chart
y = -3.0925Ln(x) + 33.83

Let x = 2

y = 23.8

I sure hope you can help me this

Thanks

Mitchel


mparke