The AGGREGATE function returns the aggregate of values in a list, table or cell range.
The tooltip for this function can be seen below and looks quite complicated.
In fact before we try and understand this function we need to make a small modification to the tooltip.
The reference syntax for this function is the one used in 99% of cases, so lets assume it's the only one that exists.
Having the other one displayed in the tooltip is unnecessary and causes users a lot of confusion.
If you want to include hidden rows, error values and nested functions the set "Options" = 4.
Exclude Hidden Rows
If you want to only exclude hidden rows then set "Options" = 5
Calculations that ignore hidden rows.
Exclude Error Values
If you want to only exclude error values then set "Options" = 6
Exclude Nested Functions
If you want to only exclude nested AGGREGATE or nested SUBTOTAL functions then set "Options" = 0.
Not currently possible
This is possible with the SUBTOTAL function. For more details refer to the Subtotals page.
Columns of Data
This function is designed for columns of data and not rows of data.
If your data is going across the page (instead of vertically) hiding a column will have no effect.
The tooltip for this function indicates that there is another form to this function.
This function will not ignore hidden rows, nested subtotals or nested aggregates if the array argument includes a calculation.
There are a number of different ways that rows can be hidden: AutoFilter, Manually Hidden and Grouping.
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext