The simple SUM function is an adequate tool to aggregate a data array in a financial model. However, there are some financial spreadsheets that will require a more robust Excel function, in order to provision for hidden rows, error values, and nested SUBTOTAL and AGGREGATE function cells.
The use of Excel AGGREGATE with SUM as the function syntax, depending on the Option selected, in terms of which values to disregard in the evaluation range for the function, will overcome certain problems with aggregating a data range. The following video and model outline this.
Please note, the Excel AGGREGATE function is not designed for rows of data or horizontal data arrays.
Overview of Excel AGGREGATE
Excel AGGREGATE function is a multi-faceted function, which is not restricted to merely the SUM of a data array. It can manage the following Excel functions – courtesy of the Function_num argument:
Further, the required Option argument dictates which values to consider or disregard in the evaluation range for the Excel AGGREGATE function.
Clearly, the Excel AGGREGATE function has broad modelling application, which is beyond the scope of this high-level blog. Follow-up blogs and videos will delve deeper into the benefits of the Excel AGGREGATE function.
Practical business planning benefits of Excel AGGREGATE with SUM function
In certain situations, a financial model will require hidden rows or contain nested SUBTOTAL and AGGREGATE function cells; the AGGREGATE function will (in certain forms) be able to overcome potential aggregation or duplication problems, which a standard SUM function would encounter and fail to overcome.
Options with the Excel AGGREGATE and SUM function
The seven Options will generate the following outputs, in the above attached spreadsheet “Cash Payments Schedule” for “Global Holdings Inc” (the Company).
0. or omitted. Ignore nested SUBTOTAL and AGGREGATE functions
It will disregard the hidden subtotals or aggregates for the Company’s 4 business units, and total these SUBTOTAL amounts together with the hidden South America and Western Europe transaction (rows 14 to 17), and unhidden Asia and North America (rows 5 to 13) transaction line items in column I.
In this example, the output value will be $421,776.
1. Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
The opposite of Option 0, whereby the function will total only the unhidden Asia and North America transaction (rows 5 to 13) cells in column I.
It will overlook all hidden transactions, and AGGREGATE totals – both hidden and unhidden.
The output value will be $268,346 in this model.
2. Ignore error values, nested SUBTOTAL and AGGREGATE functions
It is similar to Option 0, however it will disregard error values, which blog does not cover – this will be covered in a subsequent blog.
Again it will aggregate both unhidden and hidden invoice amounts.
The Schedule output value is again $421,776.
3. Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
Like Option 1, but with the added feature of ignoring error values (which this blog will not cover); Option 3 sums only visible or unhidden invoice amounts – the Asia and North America transactions.
The resulting value will be $268,346.
4. Ignore nothing
Option 4 totals all invoice amounts; unhidden cells, hidden cells and SUBTOTAL cell amounts. Practically, this is one approach to validate the SUBTOTAL amounts of the business units (Range I19:I22), and the SUBTOTAL value of all invoice amounts.
This aggregate output will be $1,265,328.
5. Ignore hidden rows
Be aware Option 5 will only disregard the hidden invoice amounts for South American and Western Europe; it will factor the Aggregate amount in cell I24, which is the total of all Invoice Amounts – both unhidden and hidden.
The total amount will be $690,122.
6. Ignore error values
Akin to Option 4, except it will disallow cells with error values; which is beyond the province of this article.
Thus, all cells both hidden and unhidden, and cell value and cell containing SUBTOTALs, will be aggregated to return the output value of $1,265,328.
7. Ignore hidden rows and error values
Analogous to Option 5, this alternative will total only the unhidden Invoice Amounts for Asia and North America, and the total output in cell I24 (containing the SUBTOTAL formula).
The output return will be $690,122.