Page 93 - Excel for Scientists and Engineers: Numerical Methods

70 EXCEL: NUMERICAL METHODS

Evaluating Series Formulas
The obvious way to evaluate a series formula is to evaluate individual terms
in the series formula in separate rows of the spreadsheet, and then sum the terms.
Figure 4-1 illustrates the evaluation of e by using equation 4-1, summing terms
until the contribution from the next term in the series is less than 1E-15.

Figure 4-1. Evaluation of the terms of a series row-by-row.
The spreadsheet calculates the value of e by using equation (4-1).
Note that some rows of calculation have been hidden.

A more compact way to evaluate the sum of a series is by summing terms in
a single worksheet formula. For example, a value for e can be calculated from
equation 4-1 by using the following worksheet formula
=I +1/FACT(1)+1/FACT(2)+1/FACT(3)+1/FACT(4)+1/FACT(5)

where we sum the first 5 terms of the series. The true value of e to 15 decimal
places) is 2.718 281 828 459 045. The formula returns 2.717 (0.06% error).
Unfortunately, most power series converge much more slowly than this, and
many more terms are required. Hence this is not a practical way to evaluate a
series in a single cell - apart from the fact that it requires a lot of typing, a
worksheet formula is limited to 1024 characters. Fortunately there are other
ways to evaluate the sum of a series in a single worksheet formula.

Using Array Constants to Create Series Formulas
An array constant is an array of values, separated by commas and enclosed in
braces, used as an argument of a function. An example of an array constant,
sometimes referred to as an array literal, is {40,21,300,10}.