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.
 
Search JabSto ::




Custom Search