Chapter 2

Exploring a Limit with a Spreadsheet - A Use of e

Consider the function y = (1+x)1/x.  How does it behave for x= 0, x= -1, and x positive and very large? What corresponding values does y take as x ranges from -1 through 0  and on to positive values?  

To assist in appreciating the problem, the function's graph for the range x = -0.999 to x =+0 .999 follows.   Although the spreadsheet cannot calculate (1+0)1/0   it does reasonably well for x = 0.000000000000093707 as is used in the chart (The y-axis line has been removed from the chart for better viewing.)


The function appears to very nearly make a right angle turn as x increases just slightly above -0.999.   The graph suggests that y will become indefinitely large as x approaches -1.

Now graph a much lower portion of the function starting at x = -0.9.


What is its value for x = 0 and how does the function behave for large positive values of x?

For a better view near x = 0 the next chart begins at x = -0.1.

Can you spot that small jump in the function at x = 0?  It occurs because it is not possible for the spreadsheet's arithmetic engine to evaluate y = 11/0.  What to do?  For a start, examine  close to x = 0.0.
The tabulated values that y attains on each side of x = 0 are shown at the top on the foregoing chart. They are much the same.  Thus, although we are unable to calculate the value at x = 0, we can be confident that its value will between the two values shown in the chart.  The value of y as x approaches 0.0 is known as e.  A more precise value for e is  2.718281828459045.  The value that is calculated for x = 0 is somewhat random.

Like Pi, e is a transcendental number.

See the next chart to explore the function's behaviour for x > = 0.1.

Once again, a near right angle!  Clearly the function approaches 1.0 when x is large.  As it only gets closer and closer to 1.0, never reaching it, it is said to be asymptotic to it.  The value 1.0 is said to be its asymptote.

The exploration of this unusual function with the spreadsheet has provided a deal of insight into its behaviour.

The forgoing charts were all produced in the spreadsheet by tabulating the expression for y.

The process of selecting manageable portions of a function is termed segmentation.  In creating approximations to functions that have extreme behaviour it is often advisable to segment such functions and design approximations that are appropriate and unique for each segment.

The constant e and a simple Differential Equation

When the rate of change or slope of a function is in proportion to the value of the function that relationship is of the form: ∂y/∂x = ky.  In numerical approximation the relationship becomes: 
∆y/∆x = ky. 

This relationship is very commonly encountered and, to name a few cases, is involved in problems concerning the accumulation of money at interest, the dilution of solutions, the cooling of warm bodies, and the velocity of chemical reactions.

An approximate solution to this differential equation is readily provided using a spreadsheet  and using small  ∆s.  For application to a cooling situation, it was constructed with 2100 1/100 of a degree steps.   See a selection of the rows in the table following:

For this application, a starting temperature of  y = 22 degrees Celsius was assumed at an initial time x = 0 minutes. 

Small steps,  1/100 of a degree, were subtracted from the starting y value to produce new y values from which ∆x was calculated. The ∆x values were accumulated to form x values.  Next, Solver was employed to set the constant k so that the temperature was 21 degrees after 15 minutes. 

(Solver is an Add-In to Excel that determines the maximum or minimum value of one cell by changing other cells. The cells you select must be related through formulas on the worksheet.)

The cell expressions for the outlined cells can be seen by clicking the cell addresses that follow:

. .
BX15 BY15 BZ15


It is reasonable to enquire about the accuracy of the numerical solution.  The solution to this differential equation can be found analytically to be of the form y = Cekx.  To verify the numerical solution, a column of Cekx  values was added to the chart, together with a column of the relative errors between y and Cekx.  The average value of the errors was then calculated. 

The constant C was adjusted, using Solver to minimize that error.  The result: an average error of less than 5/100 of 1%. 

The new table follows.

The cell expressions for the outlined cells can be seen by clicking the cell addresses that follow:

. .
CB9 CA15 CB15

The numerical approach is well confirmed by the known analytical solution of this differential equation. 

The constant C was determined by using what is called a "boundary value", in this case an observed temperature at a particular time that was evident in the environment of the problem.   For this example it could have been that the heating furnace was turned off on a cold night at t = 0 when the living room temperature was at 22 degrees Celsius.  Fifteen minutes later the temperature was observed to be 21 degrees; this boundary value determined C.  Then we were able to conclude from the table that 16 hours and 38 minutes from t = 0, the living room would have cooled to 1 degree Celsius.

Another Method for Finding a Value for e

It may be of interest that it is possible, to arrive at close approximations to both C and e by using, Solver. Because the variables C and e are not independent Solver cannot satisfactorily adjust them both at the same time.  Instead one must minimize the error using one of them and then minimize it with the other and repeat those steps until the error no longer reduces, a slowly convergent process that will result in numerical values for both C and e. 

An alternative process, which the reader might attempt as an exercise, is to write and use a macro that he designs specifically for this class of minimization problem.


When a series of payments in varying amounts is made at irregular intervals to satisfy a loan or is received as returns from an investment, it can be informative to determine the equivalent annual interest rate charged on the loan or returned on the investment.

This matter is treated in the next topic where a related use for the constant e is also shown.

Top Previous Topic Next Topic Topics