Page 94 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 4 NUMBER SERIES 71

You can use an array constant to make the evaluation of a series formula
much more compact and accurate. For example, to evaluate equation 4-1, the
formula
= 1 +SUM( 1 /FACT({ 1,2,3,4,5,6,7,8,9,10}))

returns the value 2.718 281 801 146 38 (1 x lo6 % error).

Using the ROW Worksheet Function
to Create Series Formulas

The ROW worksheet function provides a convenient way to generate a series
of integers. To illustrate the use of this function in a formula, enter the formula
=ROW( 1 : 100)
in a worksheet cell. Now highlight the formula in the formula bar or in the cell
and press F9 (Windows) or COMMAND+= (Macintosh) to display the result of the
formula. You will see the array of integers from 1 to 100, as shown below.
;
{I ;2;3;4;5;6;7;8;9;10;1 I 12; 13;14;15;16; 17;18;19;20;21;22;23;24;25;26;27;28;29;
30; 3 1 ;32;33; 34; 35;36; 37; 38; 39;40;4 1 ;42;43;44;45;46;47;48;49; 503 1 323334; 55
;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;8
1 ;82; 83; 84; 85; 86; 87; 88; 89; 90;9 1 ; 92; 93; 94; 95;96;97;98; 99; 1 00)

Using this method you can evaluate series formulas conveniently. For
example, the formula for e becomes
{=I +SUM( l/FACT(ROW(l :I 00))))
and returns a value for e of 2.718 281 828 459 05, identical to the value returned
by Excel's built-in function.
This formula is an array formula, so after typing the formula in the cell, you
must enter it by pressing CTRL+SHIFT+ENTER. Excel indicates that the formula is
an array formula by enclosing it in braces. Don't type the braces as part of the
formula; they are added automatically by Excel.
One problem associated with using the ROW function in a formula is that the
row numbers will be adjusted if you insert or delete rows. For example, if you
insert a row above the row in which the expression ROW(1:IOO) is entered, the
expression will become ROW(2:lOl). You can avoid this problem by using the
INDIRECT worksheet function, described in the next section.

The INDIRECT Worksheet Function
The INDIRECT worksheet function creates a reference specified by a text
string. Thus, for example, the formula
I
=I N D R ECT( "A 1 ")