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.

Search JabSto ::

Custom Search