Microsoft Office Tutorials and References

In Depth Information

**Using AGGREGATE to Ignore Error Cells or Filtered Rows**

Table 11.5. Functions Available in

Table 11.5. Functions Available in
AGGREGATE

AGGREGATE

The last six functions in this list require you to specify a value for k as

the fourth argument. LARGE and SMALL typically return the kth largest or

smallest value from a list. Use the fourth argument in AGGREGATE to spe-

cify the value for k. The last six functions allow for a calculated array

instead of a range of cells.

In cell F3 of
Figure 11.4
, the final argument of 3 specifies that you want the

third smallest number in the array. For LARGE, SMALL, and QUARTILE, you

should specify an integer for k. For PERCENTILE, specify a decimal between

0 and 1.

When you are trying to return results from the visible rows of a filtered

data set, you can use either SUBTOTAL or AGGREGATE. In
Figure 11.5
,
the

SUM function in D1 returns the sum of the visible and hidden rows. The

SUBTOTAL function in D2 returns the sum of the visible rows, the same as