Shim's Big Blog of Learning

Keeping an Excel journal of my running has forced me to learn a few new tricks in Excel. The latest of these tricks is the ability to create array functions.

An array function in Excel is also knows as a Control-Shift-Enter (or CSE) function, because that’s what you have to hit (instead of just Enter) once you’ve entered the function in order to tell Excel that what you’ve just entered is an array function.

So what is an array function? Well, according to Microsoft, “An array formula is a formula that can perform multiple calculations on one or more of the items in an array.” Nice. And an array, for those of you who have never heard the term, is simply a set of values. For example:

{Monday, Tuesday, Wednesday, Thursday, Friday}

is an array of days of the week.

But back to Excel. Here’s the conundrum I was dealing with. I have a spreadsheet with the following data:

Now, imagine I want to create a summary of my longest runs for each week. How is a man to manage this? Usually, finding the largest of a series of values uses the Max(RANGE) function; in Excel, this would give me the largest of a number of values. But how to select for the week? Something like CountIf would let me count up the number of instances for a given week number… and SumIf would let me sum them… but, sadly, MaxIf does not exist.

Looking for a MaxIf function, I stumbled across several references to array formulas. It turns out that, if I start with a column of week numbers, as in column D, below:

I can then create another column to display the maximum distance I’d run that week with the following:

={Max((A2:A65536=D2)*(B2:B65536))}

(Being sure to enter this formula without the curly brackets above, but hitting Control-Shift-Enter when I’m done.)

There are a couple of things to note, here:

1. To explain the logic above: the entire array, A2:A65536 (the maximum row in Excel) is checked one at a time; if the element in the array = D2 (the week of interest) a 1 (“true”) is returned, which is then multiplied by the Distance. Otherwise, a 0 (“false”) is returned. Then, this array of distances and zeros is returned, and the Max function returns the highest one.
2. Some may ask why I’ve decided to multiply these two arrays together instead of using an IF function, which should allow me a similar result. The reason is because it didn’t work, and I’m not sure why. Perhaps someone will enlighten me, some day.
3. The reason I use A2:A65536 instead of just A:A (to select the entire column) is that doing the latter gives me an error. Including the non-numeric label in the range, apparently, confuses the array interpreter. (Though in actuality, I defined the ranges as “Week” and “Distance”.)

So that’s it — my adventure in array formulas. I hope this helps someone in need of a MaxIf function!