Microsoft Office Tutorials and References
In Depth Information
Using AGGREGATE to Ignore Error Cells or Filtered Rows
return an #N/A error. The options in AGGREGATE enable you to ignore any er-
ror cells in the range.
The optionsargument controls which values are ignored. This is a simple
binary system, as follows:
To ignore other subtotals, add 0. To include subtotals, add 4.
To ignore hidden rows, add 1.
To ignore error values, add 2.
Thus, to ignore other subtotals, hidden rows, and error values, you
specify 3 (0+1+2)
3 (0+1+2) as the optionsargument.
To ignore error values but include other SUBTOTAL values, you
specify 5 (1+4)
5 (1+4) as the argument.
This calculation works out as shown in Table 11.4 .
Table 11.4. Arguments for the
Table 11.4. Arguments for the AGGREGATE
AGGREGATE Function
Function
In Figure 11.4 , the #N/A error in cell F13 causes the SUM function in F18
to also return an #N/A. If you use a 2, 3, 5, or 7 as the second argument of
AGGREGATE, you can easily sum all the other numbers as in cell F1. You can
also use other function numbers to calculate MIN, MAX, COUNT, MEDIAN,
MODE, PERCENTILE, and QUARTILE values.
Search JabSto ::




Custom Search