Linest of exponential WITH defined intercept?

[MrPez]

I'm fitting an exponential decay to some data and want to use linest to get the decay constant and r2 values.

I'm using LINEST(LN(E3:E5),C3:C5) for the decay constant, and INDEX(LINEST(LN(E3:E5),C3:C5,1,TRUE),3,1) for r2.

If I plot the trendline on a graph I get the equation being y=1.01exp(-0.105x). I want to force the y-intercept to 1, which I can do in the trendline formatting settings giving y=exp(-0.102x).

How do I get the same equation values with linest setting a y-intercept of 1?

Thanks in advance...

 

[pgc01]

Hi

You can use the third parameter of Linest for that (that you are already used in your formula for r2).

 

[MrPez]

Thanks for the reply PGC. How do I do that? That third parameter seems to want a boolean response so if I use LINEST(LN(E3:E5),C3:C5,1,true) it takes the '1' as the same as writing 'true'. Am I missing something?

 

[shg]

LINEST(ln, x) returns the best-fit m and b for f = exp(m*x+b). If you set the third argument FALSE, b is forced to zero, and f(0) = exp(m*0 + 0) = 1.

 

[MrPez]

Thanks shg

After having another look, setting 'false gives me the right result for the decay constant. The reason I hadn't spotted it, is because I was watching r2 which doesn't seem to be right? If I plot the data and use linest on the same data, r2 from linest agrees with that shown on the graph when the third component is 'TRUE' and the trendline is not forced to use an intercept of 1. But if the third component is 'FALSE' and the trendline is forced to go through y=1 the r2 values do not match.

I'm not sure why this, and for my requirements it doesn't really matter since the decay constant is what I'm looking for, but I would be interested if someone could explain this to me!

 

[shg]

You're welcome.