As a frequent user of excel, at what I would consider advanced-level expertise, I spend my days flying through data connections, pivots, all kinds of complex formulas, and even a dash of VBA. But there was still one hurdle I hadn’t jumped. For some odd reason those squiggly brackets { } (technically I believe they’re called “braces”) had intimidated me from dabbling in the world of arrays. Something felt unnatural about hitting ctrl + shift + enter before exiting a formula. What magic would happen behind the scenes if I pushed those buttons simultaneously? I understood the logic and language of Excel, so asking it to “work differently” just seemed bizarre. I found myself working around actually using arrays by means of extra columns, pivots, and very elaborate lookups and formulas.
This past week, however, I faced my fear and jumped in… three keys at a time!
Game changer!
Imagine you have a list of contact dates and clients. You want to figure out how recently each person has been contacted by running a quick summary on the data. Sure, you could throw a pivot on the data, but what if you needed it in the table format? There is not a “MAXIF” formula to perform this action. In fact, for all its strengths, there is a gap on available “IF” formulas in Excel. Enter-in arrays! Arrays give you the power to combine formulas that analyze data in tabular form without having to pivot the data.
Here’s how it works:
Let’s say you have a list of sales, and you want to be able to reach out to clients with whom you haven’t spoken in a while. Knowing the last contact date alongside their YTD sales will help you make sure that you are staying on top of communication with your best customers. Here’s a list of sales with contact dates. We can easily throw a “SUMIF” formula in to calculate the running total, per customer, per line. But finding out the most recent (or “max”) date is not so easy, because you cannot make a “MAXIF” formula. Instead, we can “nest” them with an array.
In everyday language, we need the formula to perform the following tasks:
=MAX(number1, number2,…)
where the numbers are all in column A, “Contact Date.” If we just do MAX, it won’t take the customer into account. We need to add a criterion to also look for the max date of that customer.
In theory it should be this:
=MAX(IF(Customer Name = This Row’s Customer Name, THEN return the Max date from column A, OTHERWISE return a 0)
Unfortunately it doesn’t wrap the IF with the MAX and it produces a result that is the max overall. So let’s jump into those scary squiggly braces and see what we can do.
When you use the exact same formula — but before hitting enter at the end — instead, hold down CTRL + SHIFT + ENTER. You’ll see that Excel adds braces { } around the formula. When you copy this down, the formula magically evaluates both conditions across all the data you’ve selected. Voila! You have now added analytics to your table.
NOTE: You cannot simply add braces to your formulas to make this happen. You have to hit ctrl + shift + enter to make Excel perform the array formula.
So… What’s the takeaway? What can this do for you? By performing this array formula and quickly sorting my list… looks like I better reach out to Examples R Us. They’ve spent the most and it’s been the longest since they’ve been contacted.
Imagine what arrays can do to inform your business!
Join Chris Mackin, Everon’s Director of Sales for this informative webinar. Chris has been with Everon since the company was founded in 2003. With over 15 years of telecom, data security and small business technology expertise Chris is an expert in helping small to medium size businesses select and implement the right IT solutions to increase productivity and protect their most important asset– their data!