SUMPRODUCT still works just as well.
I’ll use whatever the hell I want to use, thanks anyway. If a pivot table is the fastest way to compare 2 datasets, it will be a pivot.
2 Likes
You assume I’m working on a reasonably current version of Excel.
Sadly, that is an incorrect assumption.
Too many of my work files are subject to audit.
My auditors like to ask questions like, “how can you be sure you didn’t miss [a value] when summarizing?”. Apparently “the totals match” isn’t a sufficient check.
I’ve found that being able to refresh a pivot table and observe that nothing changes is a relatively painless way to field such auditor inquiries.
Maybe in a couple of years, I’ll get to teach corporate audit about UNIQUE…assuming that we get it after Excel 2016’s EOL forces upgrades.
3 Likes
Here is a trick that does about the same thing with old excel keystroking:
Highlight a range, (which must have a title cell at the top) then hit
[Alt]-D-F-A-O-R-T
You can remember it as (Hold) D Fart/Fort.
For my usual use-case, I’m doing this to build a mapping table: [Data field 1]×[Data field 2] = [Grouping for analysis/reporting]. Since I need to keep the original data unmolested, I’d have to do a couple of copy-paste-values before and after.
It’s certainly doable, especially if I took the time to write a macro to automate it. However, I’m a creature of habit, plus getting auditors used to changes in methodology takes even more time than writing macros.
I’ll stick to abusing pivot tables until Corporate IT concedes that it’s time to bless a newer version of Office, and UNIQUE becomes available. Of course, I’m supposed to be getting staff later this year…so juggling ancient software and auditors whom I think would make good proctologists will hopefully become someone else’s problem.
I don’t see what’s wrong with pivot tables honestly. Sure there are downsides, but there are downsides to every type of software. What’s so terrible about them? (I can code in multiple languages - I’m probably at least as good as 65% of my actuarial colleagues. So I’m not saying this because I can’t do anything else.)
Not really. As noted above, SUMPRODUCT means you have to either list your grouping fields with some new UNIQUE function that will crash any time someone opens your file in the old version of Excel. Or else constantly copy/paste anytime your source adds a new group. It also usually takes many more keystrokes to implement than a pivot table does.
Not as noted above: Also, a SUMPRODUCT on multiple fields usually looks uglier just as a formula and scares people off from trying to figure out it’s doing, than a corresponding pivot table. If people don’t understand what you’re doing, they’re more likely not to trust the results. SUMIFS are cleaner-looking, but break if your source is another file.
And if you have lots of sumproducts on lots of rows they start slowing down your file’s calc time. (Yes, I’ve done this anyway). I really like using them anyway, but not for everything.
I don’t really want to write pages and pages of dialogue of how and why I still love sumproduct like DTNF does, nor do I want to write down how and why and when I think pivot tables are the right tool.
For both, their use depends on the data. It depends on the calculation needs of the moment. It depends on if the data will get updated regularly and the need for future recalcs or not, whether it has to be readable to other people or not, etc etc
I just want to say… I think Lambda functions will put them both out to pasture once users get comfy with lambda functions.
Whoa!!! Are you…from the future???
DP and I agree on something?
Well, now I am questioning everything about my life…
1 Like
…and once enough of us are on a version of Excel that supports them? 
Don’t worry, you don’t agree that much. DP thinks you should actually use pivot tables at times.
1 Like
Eh, I’m in control of my data, so I make them for my SUMPRODUCT functions. And that’s why it works for me.
I looked up lambda functions. I appreciate your mentioning them because a new Excel capability is something I’m usually interested in. But…maybe I’m missing something, but I don’t really see why I would use one of those over a pivot table or a sumproduct. Or how.
Damn, now WE’RE agreeing!!
“Stop trying to make lambda functions happen. They’re not going to happen.”
I haven’t used lambda functions at work before, but I think they’ll mostly shine if you want a custom function to use throughout a workbook but don’t want to get into VBA and save as a macro workbook.
If have, at various times in my career, written VBA functions for various reasons. For example, before IFERROR() was introduced, I got tired of using =IF(ISERROR(foo),“”,foo) type constructs, and instead used VBA functions.
In a file I’m updating now, I have one set of calculations that is, essentially: if input is between a and b, do calculation x; if input is between b and c, do calculation y; otherwise, do calculation z. Since the file is mostly “my work” rather than something that will be reviewed by an auditor or by someone outside my team…I’m OK with it being ugly. But if the file were for consumption/review by others, I’d want something easier to follow / more elegant. LAMBDA() could fit that bill, depending on how efficiently it runs.
Some other solutions are
-IFS instead of IF (assuming you and your collaborators have the later version of Excel)
-IF with multiple rows in your formula (classic approach, with a twist to make it more readable), with IFERROR where necessary. (I don’t see why you wouldn’t use IFERROR instead of IF(ISERROR )
-Multiple columns where calculation x, y, and z are in respective (hidden? grouped?) columns
I personally prefer the middle approach. I definitely wouldn’t use a UDF (VBA function) for this. UDFs used to be very buggy, and I was traumatized (lightly speaking). They’re probably better now but I wouldn’t go back to them.
My understanding was IFERROR only evaluates the expression once, while IF(ISERROR(expression),alternative,expression) evaluates it twice (when not an error).