Microsoft Office Tutorials and References

In Depth Information

**Using Single-Cell Array Formulas**

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 function, which returns the sum of the values in the new array.

Figure 14-14 shows an example in which the range A1:A10 is named
Data
. The SMALL function is

evaluated three times, each time with a different second argument. The first time, the SMALL

function has a second argument of 1, and it returns –5. The second time, the second argument for

the SMALL function is 2, and it returns 0 (the second-smallest value in the range). The third time,

the SMALL function has a second argument of 3, and returns the third-smallest value of 2.

Figure 14-14:
An array formula returns the sum of the three smallest values in A1:A10.

Therefore, the array that’s passed to the SUM function is

{–5,0,2)

The formula returns the sum of the array (–3).

Counting text cells in a range

Suppose that you need to count the number of text cells in a range. The COUNTIF function seems

like it might be useful for this task — but it’s not. COUNTIF is useful only if you need to count

values in a range that meet some criterion (for example, values greater than 12).