Page 371 - Excel 2007 Bible

22_044039 ch17.qxp 11/21/06 11:08 AM Page 328
Part II
Working with Formulas and Functions
FIGURE 17.14
The goal is to count the number of characters in a range of text.
The array formula uses the LEN function to create a new array (in memory) that consists of the number of
characters in each cell of the range. In this case, the new array is
{10,9,8,5,6,5,5,10,11,14,6,8,8,7}
The array formula is then reduced to:
=SUM({10,9,8,5,6,5,5,10,11,14,6,8,8,7})
The formula returns the sum of the array elements, 112.
Summing the three smallest values in a range
If you have values in a range named Data, you can determine the smallest value by using the SMALL function:
=SMALL(Data,1)
You can determine the second smallest and third smallest values by using these formulas:
=SMALL(Data,2)
=SMALL(Data,3)
To add the three smallest values, you could use a formula like this:

=SUM(SMALL(Data,1), SMALL(Data,2), SMALL(Data,3)
This formula works fine, but using an array formula is more efficient. The following array formula returns
the sum of the three smallest values in a range named Data:
{=SUM(SMALL(Data,{1,2,3}))}
The formula uses an array constant as the second argument for the SMALL function. This generates a new
array, which consists of the three smallest values in the range. This array is then passed to the SUM func-
tion, which returns the sum of the values in the new array.

328