Microsoft Office Tutorials and References

In Depth Information

**Using arrays**

Table 12-3 contains examples of how Excel treats integers and decimal fractions longer than

15 digits when they are typed in cells with the default column width of 8.43 characters.

TABLE 12-3
Examples of numeric precision

Typed entry

Displayed value

Stored value

123456789012345678

1.23457E+17

123456789012345000

1.23456789012345678

1.234568

1.23456789012345

1234567890.12345678

1234567890

1234567890.12345

123456789012345.678

1.23457E+14

123456789012345

Excel can calculate positive values as large as 9.99E+307 and approximately as small as

1.00Eā307. If a formula results in a value outside this range, Excel stores the number as text

and assigns a #NUM! error value to the formula cell.

Using arrays

Arrays
are familiar concepts to computer programmers. Simply defined, an array is a

collection of items. Excel is one of the few applications that facilitate array operations, in which

items that are part of an array can be individually or collectively addressed in simple

mathematical terms. Here is some basic array terminology you should know:

An
array formula
acts on two or more sets of values, called
array arguments
, to return

either a single result or multiple results.

ā

An
array range
is a block of cells that share a common array formula.

ā

An
array constant
is a specially organized list of constant values you can use as

arguments in array formulas.

ā

Arrays perform calculations in a way unlike anything else. You can use them for worksheet

security, alarm monitors, linear regression tables, and much more.

One-dimensional arrays

The easiest way to learn about arrays is to look at a few examples. For instance, you can

calculate the averages shown in Figure 12-32 by entering a single array formula.