Chapter 18: Understanding and Using Array Formulas
18
FIGURE 18.14
The goal is to count the number of characters in a range of text.
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 can 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.