What's Your Excel Party Trick?

The very wonderful Helen Kohr told me she had been asked this question when she had gone for an interview. Every industry will have a couple of Excel functions it really hammers: in telecoms it's VLOOKUP and some financial people told me their most used function was SUMPRODUCT (okay, they weren't really financial people, they just worked in a bank). Outside the Industry Party Trick and the usual SUM and IF, I don't think I'm exaggerating if I say that most people do not use the full suite of Excel functions frequently. I mean, just when would you use WEIBULL anyway? 

What the question admits is that learning Excel or Open Office Calc or Numbers is not something you can do in a week. These are huge programs, packed with features and facilities, the learning of which exceeds any one-semester course you and I did in maths, theoretical physics, electrical engineering or computer science. Start with the Excel object model – how many times have you seen people write code to write a recordset into a worksheet range? And then coo with delight as the numbers stream across the sheet. Well, there's a method in the Range object that does it so fast you can't see it and without re-calculating each time it writes in a number. No – you can look it up yourself. Now here's the catch – it only works if you declare the variable holding the recordset as a variant, not a recordset. And no, you won't find that in the manual, I had to to trawl the web forums before I found it. Or take the financial functions: you can pretty much figure out what NPV, IRR, RATE and PMT are for without knowing too much. How about NOMINAL? Or COUPDAYSNC? You'd have to know something about bonds before you could use them. And how about WEIBULL? It's safe to say that you are never going to learn about this unless you were hired in part for your experience in statistics. Learning about the functions means learning about the reasons you would use them: that isn't trivial.

Excel is used by business analysts and they are paid to be interested only in their business. Most commercial businesses use a handful of functions – hence the party-trick approach. Why would they pay their people to learn stuff they won't use and don't need?

Because the more your people know, the better they can solve your problems. They feel more confident, they will try different approaches to problems and maybe come up with smarter solutions. Learning this stuff is good for the brain. The company is paying these people to be smart: why would it encourage them to be dumb?