Excel Puzzler: MAXIF Without Using an Array Function
Recently I lamented that Excel has functions like SUMIF, COUNTIF, and AVERAGEIF, but that it didn’t have the equivalent of a MAXIF function. I then described a valid technique that involves using an array function that can perform like a MAXIF function would. In doing so, I overlooked a built-in worksheet function that also works like MAXIF, and so that probably explains why Excel doesn’t have such a function.
This was brought to my attention by reader Rob Brebbia, who read the first paragraph or so of my MAXIF article and took it as a challenge to see if he could figure out the solution on his own. In doing so, he uncovered a function that has become lost in my Excel memory—and his as well. Based on his successful research exercise, Rob suggested we pay homage to NPR’s Car Talk show and have a periodic Excel puzzler.
Rob’s timing is excellent, as we’ve recently unveiled the AccountingWEB forums—an area where you can ask a question about pretty much anything about accounting or Excel. Although this feature is just getting off the ground, you should be able to count on a response from either the AccountingWEB staff (that’s me if you ask an Excel question), or other readers as well. To wit, I’ve started a thread on performing MAXIF without an array function. We’ll hold all comments in moderation for a week, so that everyone has a fair shot at the puzzler, and then I’ll reveal Rob’s clever alternative to my suggestion. Do keep in mind that you must log into the AccountingWEB site to post a response to this puzzler, or ask a question of your own. Registration is simple and free!