# 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.   Return to Tips and Tricks

November 2005