Microsoft Office Tutorials and References

In Depth Information

**Tip 45: Using the AGGREGATE Function**

Using the AGGREGATE Function

One of the most versatile functions available in Excel is AGGREGATE, which was introduced in Excel

2010. 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 values in hidden rows and

error values. In some cases, you can use AGGREGATE to replace a complex array formula.

The AGGREGATE function takes three arguments, but for some functions, an additional argument is

required.

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

type of calculation to perform. The calculation type, in essence, is one of Excelâ€™s other functions.

Table 45-1 contains a list of these values, with the function it mimics.

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

Argument Value
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

* Indicates a function that requires an additional (4th) argument.