Array calculations are a great way to clean up your Excel worksheets, replacing columns and columns of “calculation” data with the same logic encased in one tidy little cell. This quick tutorial takes you through a basic example to show you how array calculations are done!
In the app I’m currently working on (a tempo-tracking app for musicians), I need to analyze the results from some accuracy tests. I won’t bore you with the details—all that matters is that I’m looking for the maximum difference between two columns of data. The data looks something like this:
The standard way to do this in Excel would be to add a column for all the differences, and then put a max calculation at the bottom. Here’s what that looks like (I’ve copied out the formulas for column C just beside it):
The problem here is that extra column. Not only could it be messy to expose all your intermediate steps, it’s also limiting. Let’s say we wanted to add another data column and calculate the max difference again. It would definitely start to get cumbersome fast.
A basic array calculation lets us do all that work in a single cell. All it takes is
=MAX(B2:B12-A2:A12) and the special shortcut ⌘⇧⏎ (or CTRL⇧⏎ for Windows). That’s it! Just remember to use that shortcut instead of pressing Enter as usual, and then you’ll get this:
You’ve eliminated the need for an entire column! This is just a basic example of array calculations in Excel and you can find much more information on the Microsoft Office website. Thanks for reading and feel free to share any comments, questions or suggestions below. (Then come say hi on Twitter at @leakywellington!)