Exponential Trendline and LOGEST

The Exponential Trendline fits data to the expression

y = B exp(Ax) (1)

The LOGEST function fits data to the expression

y = B Mx (2)

 

How can we get the trendline values into a worksheet cell?

Comparing expression (1) and (2) we see that:

Mx = exp(Ax) (3)

Taking natural logarithms of both sides of (4) gives:

xLn(M) = Ax (4)

From which we see: A = Ln(M) (5)

 

Hence, the natural logarithm of the first term (M) obtained from the LOGEST function equates to the A term in the Trendline expression

In the attached worksheet the data (A5:B14) was used to make the XY chart. An exponential trendline was added to the chart giving the expression y = 3 exp(0.25x).

The range G3:H3 was selected and the formula =LOGEST(B5:B14, A5:A14) was entered and completed with SHIFT+CTRL+ENTER since it is an array function. We see that this gave M = 1.284 and B = 3.00. In I3 the formula =LN(G3) gives the result 0.25 as the value of A. This is in agreement with our expression (5) above.

There are a number of ways of obtaining the predicted y-values. These are shown in column C, D and E.

 

Alternatively, one may use LINEST to obtain the A and B values in cells see smaller diagram.

 

Oval: Sample file

 

Return to Tips and Tricks

November 2005