The AGGREGATE function allows you to sum (along with other functions like AVERAGE, COUNT, MAX, MIN, etc.) a range of cells while ignoring any cells that may contain errors as well as ignoring hidden values due to hiding rows and/or columns.
Take the following as an example:
If we sum the entire range of “Amount” values, we will receive an error due to the one cell containing a “#DIV/0” error acting as a poison pill for the entire range.
The AGGREGATE function will handle this without issue.
The AGGREGATE function is the Swiss Army Knife of functions.
AGGREGATE is 19 separate functions packaged into a single function with additional features thrown in for good measure.
When you write a formula using the AGGREGATE function, you use a code number to indicate which of the 19 functions you wish to use.
Selecting one of the numbers listed is no different than using the selected function on its own.
The benefit of using AGGREGATE comes with the next set of options.
Selecting the appropriate option value will alter the way AGGREGATE behaves when encountering errors, hidden rows, or even other AGGREGATE functions.
This is a great way to create subtotals and grand totals whereby the grand totals ignore the subtotals, avoiding the issue of double counting.
These options are also useful for when you are filtering tables and you don’t want the hidden rows to be included in the aggregations. The SUM function (along with other functions like AVERAGE, COUNT, MAX, MIN, etc.) would continue to include the values in the filtered/hidden rows.
If we were to filter the data and some of the remaining rows contained errors, the function would not only ignore all the “filtered out” rows, but also ignore the errors that remained.
Even if the errors were not present, a traditional SUM function would continue to add all the selected rows regardless of the filtered state of the range.
---------------
by
https://www.xelplus.com/
ليست هناك تعليقات:
إرسال تعليق