Array formulas…one of those things I get asked about maybe once a year. Also the very word “array” makes me go looking for bad puns…
One thing it tells me is that the questioner is a serious Excel head. They ain’t no Excel sissy, no sirree. However it does mean that they are an area that I’m slightly fuzzy on. So after being asked again a couple of months ago I decided to delve more deeply into them..As a trainer one of the things I find useful to do is explain it to myself…(yes, I know that’s sad and I should get out more often..but what can I say?)
So after looking around for a while, the best explanation I came up with was that while in “ordinary” Excel, formulas are – by and large – cell based. In the sense that your formulas generally reference a single cell which you then copy elsewhere, an array formula allows you to work with a whole range together. To use a farming analogy – to work with the flock as opposed to an individual sheep (yes, I know..a baaaadddd joke…) .
So what I am going to cover in this tutorial is first of all an introduction to array formulas and then another that explains how you can use an array function to adapt pretty much any statistical function to add an “if” dimension to it…
In the video below, I am going to show you how you could use an array formula to quickly multiply two columns together. Of course you could just do the first one and copy down but sometimes this approach may be closer to what you need. One thing to note is that before you begin the formula make sure you have the range that you want the formula to go into is highlighted. Remember this is flock not cell.
You can download the file here…Intro_array_exercise
Using Array functions to expand your IFS family (or should I say flock?)
I also found that for me one of the most useful things was how an array formula could be used to expand your range of SUMIFS/COUNTIFS/AVERAGEIFS to pretty much any function. This came on for me as a light bulb when I was working through Chandoo’s 50 ways to analyse data course. I have found the SUMIFS functions extremely useful in the sense that they allow you to add up values, but only when they are X or Y or Z. And of course once you understand how to use the SUMIFS function, COUNTIFS and AVERAGEIFS are much easier. But suppose you want to get the Max if or the Mode ifs or the Medianifs. There is no native function in Excel to do that. BUT you can do it with any of these using the array option. THAT is very useful. I’ve given you a demo in this video and in the attached file you will see that I have used the Array option with Mode/Median/Max/Min to do the same as the SUMIFS/COUNTIFS/AVERAGEIFS functions. And of course the beauty of this is that once you understand how to do it for one…
In this video, I reference how to create a dropdown list and also the AVERAGEIFS function. I don’t plan to go into any detail on this in the video as I have covered them elsewhere…
You can download the file here…array_formulas_part_two_with_if
As always if you have any questions, comments, please feel free to comment..