Excel 2010 Bible

Part II: Working with Formulas and Functions

372

The following array formula does the job without using any intermediate formulas:

{=SUM(LEN(A1:A14))}

FIGURE 16.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)