What is the AGGREGATE function in Excel
What is the AGGREGATE function in Excel – The AGGREGATE function in excel is a conglomerate of functions. We can use 19 functions from this single function. This is just a “Function of Functions” which incorporates multiple functions. It was introduced in the 2010 version. So if you are using an older version then it would reflect #NAME? errors. The excel functions that it incorporates are given below.
Let us understand the syntax for the AGGREGATE function. There are 2 different syntaxes available as shown in the below image.
- First Syntax:
- function_num: Required. A number between 1 to 19 that specifies which function to use.
- options: A number, between 0 and 7, that defines which values to be ignored in the calculation.
- Array : An array of values that the specified function is to be performed on. Also called as range.
- [k] : An integer denoting the position in the array for functions that require this additional argument (must be supplied for the ‘Large’, ‘Small’, ‘Percentile’ and ‘Quartile’ functions)
- Second Syntax:
- function_num: Required. A number between 1 to 19 that specifies which function to use.
- options: A number, between 0 and 7, that defines which values to be ignored in the calculation.
- ref1: Required. The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value.
- ref2: optional. We can use 253 ref.
The second argument option is for ignoring value when applying the function to the range and if we don’t provide any number then it will take 0 by default. The values for options are described below.
Let us do some exercise to understand it.
In the above example, we tried to find the average of a dataset. The data set is in the range of B3 to B12. We will use the AGGREGATE function here. The first argument function_num would be 1 as per the above table. Then we would use ignore nothing as we wanted to do average for the whole dataset. After that, we will B3 to B12 as our range. So finally we get our average value of 68.
That’s all for the AGGREGATE function, for now, will meet with another topic.
You can also read about the Aggregate function from the below article.
https://exceljet.net/excel-functions/excel-aggregate-function