AGGREGATE Function in excel returns the aggregate of a given data table or data lists, this function also has the first argument as function number and further arguments are for a range of the data sets, the function number should be remembered to know which function to use.
Syntax: =AGGREGATE(function_num, options, ref1, [ref2], …)
The AGGREGATE function syntax has the following arguments:
- function_num Required. A number 1 through 13 to specify the function, as shown in the Function_Num Table below.
- options Required. A number that determines which values to ignore in the evaluation range for the function, as shown in the Options Table below.
- ref1 Required. The first reference or numeric argument for which you want the aggregate value.
-
ref2 Optional. References or numeric arguments 2 to 253 for which you want the aggregate value.
Example: Let’s look at some Excel AGGREGATE function examples and explore how to use the AGGREGATE function as a worksheet function in Microsoft Excel:
Syntax: =AGGREGATE(2,1,E1:E11)
Result:
Based on the Excel spreadsheet above, the following AGGREGATE examples would return:
Syntax: =AGGREGATE(2,1,E1:E11)
Result: 253
Syntax: =AGGREGATE(1,5,E1:E11)
Result: #N/A
Syntax: =AGGREGATE(3,5,E1:E11)
Result: 11
Note: The AGGREGATE function returns the result of an aggregate calculation like AVERAGE, COUNT, MAX, MIN, etc. A total of 19 operations are available, and the operation to perform is specified as a number, which appears as the first argument in the function. The second argument, options, controls how AGGREGATE handles errors and values in hidden rows. See tables below for all available options.
