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:
Accuracy?
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 = Ce
kx. To verify the numerical solution, a column of Ce
kx values was added to the chart, together with a column of the relative errors between y and
Ce
kx. 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:
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.
Next
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.