Как пользоваться функцией AGGREGATE в Excel

The AGGREGATE function is designed to perform aggregate calculations on data in both Excel and Google Sheets. This function allows you to carry out various aggregate operations such as summing, calculating averages, finding minimum or maximum values, etc., while ignoring hidden rows and errors.

Syntax:

The general syntax of the AGGREGATE function is as follows:

=AGGREGATE(function_num, options, ref1, [ref2], …)

• function_num — a number that specifies the aggregate function to perform (ranging from 1 to 19, or from 101 to 111).
• options — a number that specifies the function options (e.g., ignore errors).
• ref1, ref2, ... — references to cell ranges or data arrays for calculation.

Example Scenarios:

1. Calculate the average of a column while ignoring hidden rows and errors:

=AGGREGATE(1, 6, A1:A3)

2. Compute the sum of values in a column, excluding rows containing text:

=AGGREGATE(9, 4, A1:A3)

3. Find the minimum value in a column, ignoring all errors:

=AGGREGATE(15, 6, A1:A3)

In conclusion, the AGGREGATE function offers a robust tool for performing aggregate calculations with options to ignore errors and hidden rows in Excel and Google Sheets.