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)