AGGREGATE

Returns an aggregate in a list or database.

Sample Usage

AGGREGATE(4, 6, A1:A11)

Syntax

AGGREGATE(function_code, options, range1, [range2,..])

• function_code - Returns an aggregate in a list or database.
  
• options - Determines how to deal with hidden rows and error values in the evaluation range for the function.
• range1 - The array or range to consider while calculating the aggregate value.
  
• range2 - [OPTIONAL] Additional arrays or ranges to consider while calculating for aggregate value.
  
  

Examples

AGGREGATE(4, 6, C1:C11) calculates the maximum value while ignoring error values in the range C1:C11.

Notes

• Use the following index to choose a function_code.
  • 1 for AVERAGE
  • 2 for COUNT
  • 3 for COUNTA
  • 4 for MAX
  • 5 for MIN
  • 6 for PRODUCT
  • 7 for STDEV.S
  • 8 for STDEV.P
  • 9 for SUM
  • 10 for VAR.S
  • 11 for VAR.P
  • 12 for MEDIAN
  • 13 for MODE.SNGL
  • 14 for LARGE
  • 15 for SMALL
  • 16 for PERCENTILE.INC
  • 17 for QUARTILE.INC
  • 18 for PERCENTILE.EXC
  • 19 for QUARTILE.EXC

• Use the following index to choose options:
  • 0 (default) to ignore nested SUBTOTAL and AGGREGATE functions
  • 1 to ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
  • 2 to ignore error values, nested SUBTOTAL and AGGREGATE functions
  • 3 to ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
  • 4 to ignore nothing
  • 5 to ignore hidden rows
  • 6 to ignore error values
  • 7 to ignore hidden rows and error values