“AGGREGATE”
Used for – “AGGREGATE” function in MS Excel is used to perform a number of
functions of excel while your database having errors or hidden values due to
which those functions can’t give direct results while we apply them.
Syntax – “AGGREGATE” Function can be used in two different form –
Reference Form and Array Form.
Syntax for Reference Form: –
AGGREGATE(function_num, option, ref1, [ref2], …)
function_num – It is a numeric value starts from 1 to 19 and is used to tell the excel which core function we want to use for a set of data. The different number which denotes the different functions are as under. It is also a compulsory field and if you did not fill any value excel consider it as 0 and does not give any error.
function_num – It is a numeric value starts from 1 to 19 and is used to tell the excel which core function we want to use for a set of data. The different number which denotes the different functions are as under. It is also a compulsory field and if you did not fill any value excel consider it as 0 and does not give any error.
| Function num |
Function |
Function num |
Function |
Function num |
Function |
Function num |
Function |
| 1 |
AVERAGE |
6 |
PRODUCT |
11 |
VAR.P |
16 |
PERCENTILE.INC |
| 2 |
COUNT |
7 |
STDEV.S |
12 |
MEDIAN |
17 |
QUARTILE.INC |
| 3 |
COUNTA |
8 |
STDEV.P |
13 |
MODE.SNGL |
18 |
PERCENTILE.EXC |
| 4 |
MAX |
9 |
SUM |
14 |
LARGE |
19 |
QUARTILE.EXC |
| 5 |
MIN |
10 |
VAR.S |
15 |
SMALL |
option – It is
also a numeric value starts from 0 to 7 describing a total of eight conditions
for taking result as per function_num. These options were mentioned below with
their behaviour. It is compulsory field and if you did not fill any value excel
consider it as 0 and does not give any error.
| 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 |
ref1 – It is a
compulsory field and is first argument out of multiple argument (dependent upon
the first numeric value you have put into the area of function_num) for which
you want a aggregate of the field.
[ref2] – It is
an optional field and are arguments starting from 2 to 255 for the same as of
ref1.
Syntax for Array Form: –
AGGREGATE(function_num, options, array, [k])
function_num – This is same as in reference form.
option – This is same as in reference
form.
array – This is a table or you can say a group of rows and columns in
which you want to a specific operation as per numeric code entered in
function_num. It is compulsory field without which formula results in error.
[k]
– This is an optional field and is required as per different functions. Some of
the function(along with syntax) which required this field are mentioned below:-
LARGE(array,k)
|
QUARTILE.INC(array,quart)
|
SMALL(array,k)
|
PERCENTILE.EXC(array,k)
|
PERCENTILE.INC(array,k)
|
QUARTILE.EXC(array,quart)
|
Example – Following example will clear
some concept of AGGREGATE function:-
Here Cell A1 to B7 have some data
along with some error in it. Now Let us understand how AGGREGATE works
beautifully ignoring these errors while the direct formulas available in excel
result in error when we try to use them on the same data. Here Column D
contains the Formulas using AGGREGATE Function and column E contains direct
formulas available for getting the same result and column F contains the direct
formula which does not contains the error cell.
A
|
B
|
C
|
D
|
E
|
F
|
G
|
|
1
|
DIV/0!
|
40
|
Formula of aggregate
|
Direct Formula
|
Alternate Right Formula
|
||
2
|
6
|
33
|
=AGGREGATE(4,6,A1:A7)
|
=MAX(A1:A11)
|
=MAX(A2,A3,A5:A11)
|
||
3
|
10
|
12
|
=AGGREGATE(14,6,A1:A7,3)
|
=LARGE(A1:A11,3)
|
|||
4
|
#NUM!
|
18
|
=AGGREGATE(15,6,A1:A7)
|
=SMALL(A1:A11,)
|
|||
5
|
19
|
49
|
=AGGREGATE(12,6,A1:A7,B1:B7)
|
=MEDIAN(A1:A7,B1:B7)
|
=MEDIAN(A2:A3,A5:A7,B1:B7)
|
||
6
|
22
|
31
|
|||||
7
|
13
|
5
|
And
result you get as under:-
A
|
B
|
C
|
D
|
E
|
F
|
G
|
|
1
|
DIV/0!
|
40
|
Formula of aggregate
|
Direct Formula
|
Alternate
Right Formula
|
||
2
|
6
|
33
|
22
|
#NUM!
|
22
|
||
3
|
10
|
12
|
13
|
#NUM!
|
|||
4
|
#NUM!
|
18
|
#VALUE!
|
#NUM!
|
|||
5
|
19
|
49
|
18.5
|
#NUM!
|
18.5
|
||
6
|
22
|
31
|
|||||
7
|
13
|
5
|
This
is clear from the result table where we provide the all input data the function
AGGREGATE results in right way and ignored the errors in the data. But all the
Direct formulas available will result in error because our data table have
errors.
NOTE :- Please note that you don’t need to remember all this list because
as soon as you type the function_num argument when you enter the AGGREGATE function
into a cell on the worksheet, you will see a list of all functions that you can
use as arguments.
While using the AGGREGATE
function it should be kept in mind that this function is designed for columns
of data or vertical ranges. It is not designed for rows of data, or horizontal
ranges. For example, when you subtotal a horizontal range using option 1, such
as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum
value. But, hiding a row in vertical range does affect the aggregate.