Microsoft Office Tutorials and References

In Depth Information

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 ar-

ray, 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-15 shows an example that sums the three smallest values in the range A1:A10. 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 ar-

gument of 3 and returns the third-smallest value of
2
.

Figure 14-15:
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