This follow-up article on the use of Excel AGGREGATE SUM introduces the added complexity of error cells, text cells or other erroneous cells that would cause errors with a standard Excel SUM function.
In essence a standard Excel SUM function results in garbage out from garbage in!
The previous article on the value of the Excel AGGREGATE SUM in financial modelling, only briefly explored this added challenge, which can be a major issue in some financial models.
The below model is showcased in the following video, which highlights how some of the Excel AGGREGATE SUM permutations via the Option argument, can overcome such erroneous cells in a range.
Strategize Financial Modelling – Excel AGGREGATE SUM & Errors.xls
Remember the Excel AGGREGATE function does not work for rows of data or horizontal data arrays.
Options with the Excel AGGREGATE SUM function
Just like the previous blog, the seven Options will generate the following outputs, in the above attached model “Cash Payments Schedule” for “Global Holdings Inc” (the Company).
0. or omitted. Ignore nested SUBTOTAL and AGGREGATE functions
Although it will overlook the hidden subtotals or aggregates for the Company’s 4 business units, it will be unable to disregard erroneous cells in the sum range.
The output value will be #VALUE!
1. Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
Similar to Option 0, although it can discount the hidden subtotals, aggregates and cells; the formula will still account for unhidden erroneous cells in the sum range.
The calculated output will be #VALUE!
2. Ignore error values, nested SUBTOTAL and AGGREGATE functions
Although this option will disregard calculated subtotals or aggregates, it will sum both unhidden and hidden, non-erroneous cells in the relevant range.
Remember a financial model will hide certain rows or columns for a reason. The reason being to disregard from a specific business planning exercise.
The estimated output will be $264,880.
3. Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
In most financial modelling situations, this is the favourable approach to adopt, because the Excel AGGREGATE SUM will only amass unhidden, non-erroneous cells in the designated range.
The computed value will be $147,700.
4. Ignore nothing
As the name suggests, the Excel AGGREGATE SUM will calculate all cells – including subtotals or aggregates. Thus it will be unable to discriminate out erroneous cells.
The computed output will be #VALUE!
5. Ignore hidden rows
Like Option 4, but excluding the hidden rows, this alternative Excel AGGREGATE SUM will be produce an erroneous value.
It will generate an output of #VALUE!
6. Ignore error values
This option will distinguish only non-erroneous cells, however it will also aggregate the duplicating subtotal or aggregate outputs in the sum range.
Its calculated output will be $1,108,432.
7. Ignore hidden rows and error values
Essentially an alternative, preferred approach to Option 5. In most financial spreadsheets, this application of Excel AGGREGATE SUM will rightfully sum only the non-erroneous, unhidden cells in the range.
The generated output will be $147,700.
Upholding the tenet of Best Practice Financial Modelling
Undoubtedly the use of Options 5 or 7 will be the preferred approaches to the use of the Excel AGGREGATE SUM function, because they are able to fully meet the following challenges of modelling erroneous and non-erroneous cells in a financial model. These two approaches help in achieving a best practice financial model, which highlights the following elements of value:
• Overcomes problems with some of the other AGGREGATE Sum scenarios in the previous blog and this article;
• Delivers an immediate sum of a preliminary range, which is essentially a work in progress;
• Avoids the problem of one or two erroneous cells preventing the operation of a spreadsheet;
• Avoids the embarrassment of presenting no calculated or preliminary outputs due to one erroneous input cell; and
• Protects various input users and their work, thanks to one input user entering an erroneous input cell into the entire spreadsheet
.