AGGREGATE

“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	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.

• Tweet
• Share
• Share
• Share
• Share