Microsoft Office Tutorials and References
In Depth Information
Using the New AGGREGATE Function
Table 113-2: Values for the Second Argument of the AGGREGATE Function
Option
Behavior
0 or omitted
Ignore nested SUBTOTAL and AGGREGATE functions
1
Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2
Ignore error values, nested SUBTOTAL and AGGREGATE functions
3
Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4
Ignore nothing
5
Ignore hidden rows
6
Ignore error values
7
Ignore hidden rows and error values
The third argument of the AGGREGATE function is a range reference for the data to be
aggregated.
The SUBTOTAL function always ignores data that is hidden, but only if the hiding is a
result of autofiltering or contracting an outline. The AGGREGATE function works
similarly, but also ignores data in rows that have been hidden manually. Note that this
function does not ignore data in hidden columns. In other words, the AGGREGATE
function was designed to work only with vertical ranges.
Figure 113-1 shows an example of how the AGGREGATE function can be useful. The worksheet
contains pre-test and post-scores for eight students. Note that Diane did not take the post-test,
so cell C8 contains the NA/# error value (to indicate not available).
Cell D11 contains a formula that uses the AVERAGE function to calculate the average change. This
formula returns an error:
=AVERAGE(D2:D8)
The formula in cell D12 uses the AGGREGGATE function, with the option to ignore error values:
=AGGREGATE(1,6,D2:D8)
 
Search JabSto ::




Custom Search