Array of Hope

My love of spreadsheets is well attested. Here's another post about them...

I've been using Google Sheets a lot more lately. I love Excel, of course, and there are things that Excel can do which Google Sheets cannot. But increasingly, the things Google Sheets can do which Excel cannot are proving far more useful to me. Some things, like the flexibility of being able to employ close-topped but open-ended ranges (e.g. A3:A) are just convenient little time-savers, but they are emblematic of something very important: Google Sheets is designed to handle growing data sets in a way which Excel was not.

Don't get me wrong, Excel has the capacity to cope with growing data: it's the "Table" option on the "Insert" tab. If you are likely to be continuously adding data to an Excel spreadsheet, and want to ensure that any formulae expand accordingly, then format your dataset as a Table. It's good, but it isn't easy to operate across multiple sheets. Things start to get knotty. Compared to Google Sheets, Excel's solution is primitive.

Google solves the problem by using =ARRAYFORMULA() - If you stick a formula in the brackets and change any cell references to ranges (e.g. replace an A2 with an A2:A), it will expand the formula down the page.

Excel also has array formulae. But in Excel they're a kind of bewildering black magic that involves pressing three keys simultaneously. Learning how they work pretty-much involves several days of macheteing your way through Stack Overflow questions. Save the Blank Skip example I mentioned on this blog, for now we'll ignore these curly-bracketed monsters. 

Google's =ARRAYFORMULA() are just so ridiculously straightforward to use. And they work easily across sheets. You will never have to expand a formula down the page ever again. They are the future of spreadsheets.

Here's an example... Two coins are being tossed. I've done four tosses but I'll probably toss some more later. Column C uses =ARRAYFORMULA() and a nested IF (the first IF gives "" if there's nothing in column A for that row, just to keep things tidy; the second IF converts my tosses into binary):

	A	B	C
1	Coin 1	Coin 2	Coin 1 = Head?
2	Heads	Tails	=arrayformula(if(A2:A="","",if(A2:A="Heads",1,0)))
3	Tails	Heads	0
4	Heads	Heads	1
5	Heads	Tails	1
6

The formula completes down the column automatically. It's lovely.

=ARRAYFORMULA() works with most functions, but not all. AND and OR don't seem to work, and some other formulae will only spring into life if you dress them up in an IF. Things like COUNTIF and COUNTIFS break things because they rely on range statements and that seems to get in the way of the ARRAYFORMULA syntax. But there are ways around such things.

If we wanted to keep a live count of the total number of Heads in column A we could join all the cells in A together, count the length, and compare it with the length were we to remove all instances of the word "Heads":

=arrayformula(if(A2:A="","",(len(concatenate(A2:A))-len(substitute(concatenate(A2:A),"Heads","")))/len("Heads")))

This is a bit of a long winded workaround, but it does the trick. We can even get a COUNTIFS effect. This formula checks for Heads in both columns by joining A and B together with "&" and comma-separating them (&","), before applying the COUNTIF workaround:

=arrayformula(if(A2:A="","",(len(concatenate(A2:A&B2:B&","))-len(substitute(concatenate(A2:A&B2:B&","),"HeadsHeads","")))/len("HeadsHeads")))

Here's a nonsense example, testing the probabilities of coins. As the data increases, so does the understanding of any bias in each coin. Conditional formatting (custom formulae of =$M1="No" and =$M1="Yes" respectively) highlights any combinations that do not fall within ±5% of the expected 25% probability. Try adding more rows of data to columns A and B, to see if you can make the coins appear any more or less biased.

In addition to =ARRAYFORMULA() there's also handy tools like =SORT(), =FILTER(), =UNIQUE() and the amazing =QUERY() that we can use on growing sets of data. If you're likely to be importing fresh data-sets, you could set up a SORT and FILTER (or QUERY) formula on a second sheet to rearrange the data into something more useful. Things like this are doable in Excel, but they require you to click buttons. That Google does all of this in the formula bar, and automatically, is what makes it my tool of choice for anything that's likely to grow. The only drawback is that Google is a bit slow. The more data you throw at it, the more cups of coffee you'll need to go and make. But I can live with that.