Page 95 - Excel for Scientists and Engineers: Numerical Methods

72 EXCEL: NUMERICAL METHODS

entered in a cell (other than cell Al, of course) creates a reference to cell A1 and
returns the value contained in cell Al. Since the reference is text, it will not
change to A2 if a row is inserted above. The INDIRECT function can be used to
create powerful and versatile worksheet formulas. Some examples will serve to
illustrate.
The formula
=INDIRECT(BI)
(notice the absence of quotation marks) returns the value in cell A27 if cell B1
contains the text value A27.
Since the argument of INDIRECT is a text string, the use of the concatenation
operator (the "&" character) is common. For example, the formula
=INDIRECT("A' & 61)

returns the value in cell A27 if cell B1 contains the value 27.
Using the INDIRECT Worksheet Function
with the ROW Worksheet Function
to Create Series Formulas
The INDIRECT function can be used with the ROW function to create
formulas to evaluate number series. The series formula for e that was shown
previously becomes the formula
(=1+SUM(1/FACT(ROW(INDIRECT("1:20"))))}
if you wish to evaluate the first 20 terms, or
{=I +SUM( 1 /FACT(ROW(INDIRECT("l :"&BI ))))}
where the value in cell B1 specifies the number of terms to be evaluated. For
some, but not all, series you can evaluate 65536 (216) terms conveniently in this
way.
Again, you must enter the array formula by pressing CTRL+SHIFT+ENTER.

The Taylor Series

A series known as the Taylor series is frequently used in the evaluation of
functions by numerical methods. The Taylor series for the evaluation of a
function F at the point x + h, given the value of the function and its derivatives at
the point x, is
F(x + h) = F(x) + C Fk (x)hk +5 (4-3)

k=l k!