Member Since: 17th Jul 2009
Chartered Accountant with extensive experience in management reporting, Microsoft Excel and business process improvement.
Also runs a spreadsheet development business at http://www.needaspreadsheet.com.
Provides consulting on the above specialisms as well as providing cost savings to clients at no cost.
Editor and chief contributor to Not Just Numbers Ezine - The ezine for those who know it's not just about the numbers.
Cost Saving for SMEs, Overhead reduction, Excel, Management Reporting, Process Improvement, PISO.
3rd Mar 2017
I didn't know that either! I do find it amazing though, how many times SUMPRODUCT saves the day when all else fails!
2nd Mar 2017
Thanks. I didn't realise that, although I do try to avoid linking between files in formulae, as I have seen so many problems resulting from it!
11th May 2016
Thanks for the heads up.
11th May 2016
I am not aware of this rule. I have been posting my blog posts on AccountingWeb since 2009. Every post is a copy of a post on my Not Just Numbers blog, and indeed starts with the line "From the Not Just Numbers blog".
For most of those years AccountingWeb has treated my posts as a Featured Blog (although I'm not sure how that works in the new layout!), which I would find surprising if they were against their rules.
31st Dec 2013
Thanks - always nice to be appreciated!
24th Sep 2013
Good to know
Thanks for that. A handy addition to the article.
22nd Nov 2012
DOLLAR works well for that application
Thanks for the comment.
Yes, DOLLAR works well as an alternative to TEXT for the currency format.
I'd used TEXT in the post due to its wider application.
15th Jun 2012
Pivot Tables vs SUMIF
Great post, Simon.
I thought I'd add my two penn'orth on the Pivot Tables vs SUMIF debate.
Both have their roles and limitations, but I find a combination of Pivot Tables and GETPIVOTDATA gives the best of both worlds.
Some of the big advantages of Pivot Tables are:
They summarise ALL of the data, not just those codes you've included in your formulae - this is particularly important when handling new nominal ledger codes;They are very transparent, you can drilldown to the raw data that makes up any number.
The main advantage of SUMIF, I believe, is that it gives complete flexibility over the presentation. This is a big advantage, as a pivot table might be great for presenting various reports in a management accounts pack, but does not look like a set of accounts.
I take the following approach, which gives the flexibility of SUMIF with the robustness and transparency of Pivot Tables:
Cross reference table to map nominal ledger codes to lines of the management accounts;VLOOKUP to pull this management accounts code alongside the TB data;A pivot table to summarise the TB by management accounts code;Two extra reference columns to the left of the management accounts to show the management accounts code and whether the sign should be reversed (with a +1 or -1);A GETPIVOTDATA formula where you would have used a SUMIF, to pull the numbers from the pivot table, multiplied by the sign reversal column. With clever use of the dollar signs, you can copy the same GETPIVOTDATA formula throughout the whole set of accounts.
16th May 2012
I agree. Using Excel is certainly not wrong, but people need to be shown how to use it and use it responsibly.
Any sophisticated tool can cause havoc in the wrong hands, or even in the right hands without adequate training!
11th May 2012
Risk and Denial
I think often this risk can be part of the driver for the denial of Excel's use. If senior management blindly assume that all important data is handled within the ERP or accounting system, then they don't need to grasp this thorny issue!