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
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
Search JabSto ::

Custom Search