Microsoft Office Tutorials and References

In Depth Information

**Using the New AGGREGATE Function**

Using the New AGGREGATE Function

One of the new worksheet functions introduced in Excel 2010 is AGGREGATE. You can use this

multipurpose function to sum values, calculate an average, count entries, and more. What makes

this function useful is that it can (optionally) ignore hidden cells and error values.

The first argument for the AGGREGATE function is a value between 1 and 19 that determines the

calculation type. The calculation type, in essence, is one of Excelâ€™s other functions. Table 113-1

contains a list of these values, with the function it mimics.

Table 113-1:
Values for the First Argument of the AGGREGATE Function

Function_num

Function

1

AVERAGE

2

COUNT

3

COUNTA

4

MAX

5

MIN

6

PRODUCT

7

STDEV.S

8

STDEV.P

9

SUM

10

VAR.S

11

VAR.P

12

MEDIAN

13

MODE.SNGL

14

LARGE

15

SMALL

16

PERCENTILE.INC

17

QUARTILE.INC

18

PERCENTILE.EXC

19

QUARTILE.EXC

The second argument for the AGGREGATE function is an integer between 0 and 7 that specifies

how hidden cells and errors are handled. Table 113-2 summarizes these options.