TechTip: DB2 Web Query and Excel Spreadsheets: Easy as Pie! Part III

[jvoris]

** This thread discusses the article: TechTip: DB2 Web Query and Excel Spreadsheets: Easy as Pie! Part III **
In Excel, instead of using SUM(D3136)
. . . a better formula is SUBTOTAL(9,D31: D36). This formula is the same as SUM() but it allows grand totals to be added at the end of the column without double-counting the lines with SUM()
- John Voris

[G.Cobb]

That works too!

** This thread discusses the article: TechTip: DB2 Web Query and Excel Spreadsheets: Easy as Pie! Part III **
John - thanks for the input. That is certainly another technique for doing column subtotaling. In hindsight, I should have shown you the generated formulas for grand totals (which my example report did have).
Web Query keeps track of all the subtotal cells -so the formula generated for grand totals is actually summing the subtotal cells for that column. This provides the correct grand total values and we avoid double counting.
For example, say the subtotal cells in my sample report were D37, D42, D49, D54, and D58. The formula generated for the grand totals cell for that column would be as follows:
=SUM(D3742,D49,D54,D58)
Both techniques provide a valid grand total value. So from a performance perspective, which one is better? I don't pretend to be an Excel performance expert, but my guess is that
the Web Query technique might be a bit more efficient - simply because its performing fewer calculations. But perhaps inernally Excel is processing all the same anyway. If there is a difference in performance, its probably negligible.

Thanks again for your comments. It's nice to know someone actually reads my articles and pays such close attention!

[G.Cobb]

Whoops - correction!

** This thread discusses the article: TechTip: DB2 Web Query and Excel Spreadsheets: Easy as Pie! Part III **
I stand a corrected (a bit anyway) - Web Query is actually keeping track of the cell ranges of the rows with data (not the subtotal cells) and using them in the grand totals cells. Example grand total cell formula:
=SUM(D136,D3841,D4348,D5053,D5557)

Either way, the result is the same - no double counting. It does seem like John's suggested technique would be easier to implement.

My apologies for the confusion. My vision is a bit foggy today...should've been wearing my bifocals!