AGGREGATE function in MS EXCEL

In Excel, the AGGREGATE function is a powerful tool that allows you to perform calculations on a range of data while ignoring hidden rows, error values, or other specified conditions. Here’s the syntax and usage of the AGGREGATE function:

AGGREGATE(function_num, options, ref1, [ref2], ...)

• function_num: This argument specifies which function to use for aggregation. It is a number between 1 and 19 or between 101 and 111, depending on the function you want to use. The functions available include SUM, AVERAGE, COUNT, MAX, MIN, etc. You can find the complete list in Excel’s documentation.

• options: This argument specifies which values to ignore in the calculation. It is a number between 0 and 7 or between 9 and 13. Commonly used options include ignoring hidden rows (2), error values (6), or both (7).

• ref1, [ref2], ...: These are the references to the ranges or arrays you want to aggregate. You can specify up to 255 ranges or arrays.

Examples:

1. Calculate Average Ignoring Hidden Rows:

   To find the average of a range (A1:A10) while ignoring any hidden rows:

   excel
   
   =AGGREGATE(1, 5, A1:A10)
   

   • 1 indicates the average function.
   • 5 means ignore hidden rows.

2. Find Maximum Value Ignoring Errors:

   To find the maximum value in a range (B1:B10) while ignoring any error values:

   excel
   
   =AGGREGATE(4, 6, B1:B10)
   

   • 4 indicates the maximum function.
   • 6 means ignore error values.

3. Calculate Median Excluding Hidden Rows and Error Values:

   To calculate the median of a range (C1:C10) excluding both hidden rows and error values:

   excel
   
   =AGGREGATE(3, 6, C1:C10)
   

   • 3 indicates the median function.
   • 6 means ignore error values.