AGGREGATE in Excel (Table of Contents)
AGGREGATE in Excel
- The AGGREGATE function was introduced in Microsoft Excel 2010, not available in Excel 2003 or 2007 version
- It acts as a worksheet function & is an inbuilt function in Excel that is categorized under Math/Trig Function
- Excel AGGREGATE has an additional built-in option and function that allows you to ignore hidden rows, errors & nested subtotals, it is more powerful & advanced form compared to others
- It is so versatile, it can replace the below mentioned 19 most commonly used Excel functions in statistics at one go (Mentioned in page 2)
Definition
“Returns the AGGREGATE in a database of values or list”
It means it performs a number of calculations (19 Excel statistical functions)
AGGREGATE Formula in Excel
AGGREGATE function have two type of formula
It can be noticed when you type AGGREGATE function in an excel cell
1) AGGREGATE in Reference form
2) AGGREGATE in Array Formula
Function_num: It is a number, it can be from 1 to 19, It depends on which specific function you want to use in the below-mentioned list
Here each number represents a function, it is a compulsory argument
1 to 13 are Reference form and 14 to 19 are array form
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
Options: is the number 0 to 7 that specifies which values to ignore for the aggregate function Note: If the options parameter is omitted, by default options is set to 0
0: Ignore nested SUBTOTAL and AGGREGATE functions
1: Ignore nested SUBTOTAL, AGGREGATE functions, and hidden rows
2: Ignore nested SUBTOTAL, AGGREGATE functions, and error values
3: Ignore nested SUBTOTAL, AGGREGATE functions, hidden rows & error values
4: Ignore nothing
5: Ignore hidden rows
6: Ignore error values
7: Ignore hidden rows and error values
ref1, ref2, ref[3]: It is a first numeric argument for the function when using the REFERENCE syntax. it is values or numeric value on which we want to perform the computation, Here minimum two arguments are required, rest arguments are optional, For second reference, Numeric arguments should be between 2 to 253 for which you want the aggregate value
Array: An array or refers to a range of cells when using the ARRAY syntax
[k]: The last 6 functions (under 1 to 19 function list): k value as a fourth argument
It is an optional argument, it is used, if we need to find out LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, or QUARTILE.EXC when using the ARRAY syntax
How to Use the AGGREGATE Function in Excel?
This AGGREGATE function is very simple easy to use. Let us now see how to use the AGGREGATE Function with the help of some examples.
Example #1
The following table contains yearly sales data (2015)
If you run the =SUM (B8:B16) function directly in cell B17,
it gives the correct value because that column does not contain hidden rows, errors & nested subtotals
Here will calculate the SUM using an AGGREGATE function in cell B19.
=AGGREGATE(9,4,B8:B16),
The result will be 487
Function_ num: For SUM function, the function_ num is 9
Option: In Column B, i.e. For 2015-year sales data, all values are given and we won’t have to ignore any values, it does not contain hidden rows, errors & nested subtotals. so, we will select Option 4 (ignore nothing)
Array: It is a range for which you want to calculate aggregate functions. Here reference range of values is B8:B16, it is selected as an array of numeric values
‘k’ is an optional argument, is used only for a function like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC. we are calculating the SUM here, so we will omit the value of k.
Example #2
The following table contains yearly sales data (2016)
In column C, for the 2016 yearly sales data. In the range C8:C16, a cell C11 & C12 contains an error value (#DIV/0! & #N/A), In the AGGREGATE formula, when an appropriate option is used, the AGGREGATE in Excel gives the correct SUM value, neglecting the error value.
If you run the =SUM (C8:C16) function directly in cell C17,
it returns an error value, due to an error in that range. because that column contains #DIV/0! & #N/A errors.
In order to ignore the error values, we have to use option 6 in an AGGREGATE function
=AGGREGATE(9,6,C8:C16).
The result or output will be 334
Function_ num: For SUM function, the function_ num is 9
Option: In Column C, i.e. For 2016-year sales data, In the range C8:C16, a cell C11 & C12 contains an error value (#DIV/0! & #N/A). To ignore these errors, we will select Option 6 (Ignore error values)
Array: It is a range for which you want to calculate aggregate functions. Here reference range of values is C8:C16, it is selected as an array of numeric values
‘k’ is an optional argument, is used only for a function like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC. we are calculating the SUM here, so we will omit the value of k.
when an appropriate option is used in the AGGREGATE function, the AGGREGATE in Excel returns or gives the SUM of the remaining values neglecting the error value in cell C11 & C12. i.e. 334
Example #3
The following table contains yearly sales data (2017)
In column D, for the 2017 yearly sales data. In the range D8:D16, a cell D9 is a blank cell or hidden row & D12 contains an error value (#N/A), In the AGGREGATE formula, when an appropriate option is used, the AGGREGATE in Excel gives the correct SUM value, neglecting the hidden row & error value.
If you run the =SUM (D8:D16) function directly in cell D17,
it returns an #N/A error value, due to an error in that range. because that column contains #N/A error & hidden row or blank value.
=AGGREGATE(9,7,D8:D16),
The result or output will be 262
Function_ num: For SUM function, the function_ num is 9
Option: In column D, for the 2017 yearly sales data. In the range D8:D16, a cell D9 is a blank cell or hidden row & D12 contains an error value (#N/A). To ignore these errors, we will select Option 7 (Ignore hidden rows and error values)
Note: you can hide rows by selecting the row(s) > right-click on specific row > Hide
Array: It is a range for which you want to calculate aggregate functions. Here reference range of values is D8:D16, it is selected as an array of numeric values
‘k’ is an optional argument, is used only for a function like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC. we are calculating the SUM here, so we will omit the value of k.
when an appropriate option is used in an AGGREGATE function, the AGGREGATE in Excel returns or gives the SUM of the remaining values neglecting the error value in cell D9 & D12. i.e. 262
Things to Remember about the AGGREGATE Function
- The AGGREGATE function is applicable only for vertical ranges or columns of data. It is not designed for horizontal range or rows of data.
- It has a limitation; it only ignores the hidden rows, It does not ignore the hidden columns
- An AGGREGATE function is applicable only for the numeric value
- Function _ num argument value should not be greater than 19 or less than 1, similarly for option argument should not be greater than 7, otherwise, it will give #VALUE! error
- In AGGREGATE function, if in function number argument, if you are using 14 to 19 (LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC), the “K” argument should be used. g. =AGGREGATE(15, 6, A1:A9, 3). If in case, “K” value or second reference argument is ignored, it will result in a #VALUE! error
Recommended Articles
This has been a guide to AGGREGATE Function. Here we discuss the AGGREGATE Formula and how to use AGGREGATE Function along with practical examples and downloadable excel templates. You can also go through our other suggested articles –
