Making it all add up
One of the great new features in Monarch v9 is the ability to include Excel formulas when exporting a Monarch summary with simple Sum key level subtotals to an Excel file. This lets you do some what-if work with the exported data much more easily than was possible with prior Monarch versions, as in those versions the subtotals and the grand totals were exported as values, not formulas.
The alert Excel user will notice not just the fact that formulas are created, but exactly how the formulas are constructed.
Monarch creates the formulas not with the commonly used SUM function, but the SUBTOTAL function. Why is that?
The SUBTOTAL function should be the spreadsheet designer’s function of choice for creating totals, because it makes building sheets with multiple total levels a breeze. The reason for that is not so much about the beginning, or the middle, so much as it is about the end.
Imagine your sheet has thirty SUM functions which all appear in column C only. How do you write the function to give you the grand total in column C?
You might go with:
=C10+C20+C30+C40+…+C300
and eventually build a really long formula.
Alternatively, you could build a SUM of SUMs, like:
=SUM(C10,C20,C30,C40,…,C300)
Not much different in the end.
Instead you use the SUBTOTAL function. The SUBTOTAL function’s secret weapon is that it ignores other SUBTOTAL functions within the specified range. First you’d create SUBTOTAL functions in C10, C20, etc., then build the formula to total the column.
To do just that, simply use:
=SUBTOTAL(9,C1:C300)
This makes it quick and easy. You never have to worry about missing an amount somewhere, or getting an incorrect total by picking up an amount more than once. Plus, it makes auditing later on the sheet much easier.
What’s that 9 doing in there? That tells the SUBTOTAL function what you want it to do, because this function can do more than simple addition.
This table shows what you can have the function do for you.
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP
By supplying the values 1 through 11 for the first parameter of the function, it will do its magic on all of the rows in the range you specify.
If you add 100 to the value, and specify 101 through 111, the function will ignore any values in any hidden rows in your specified range. Be careful about using this approach however, as you might not get the results you expect, especially if you’re using Excel’s outlining tools on your sheet.
We supplied a 9 to get SUM values, but we could have supplied a 1 to get averages, or a 4 to get the maximum values in the range.
The SUBTOTAL function should definitely be a part of your spreadsheet design toolkit. You can definitely see why the Monarch programmers use it instead of the SUM function; they want you to Excel with Monarch! 
