Referencing Dynamic Array Formulas

One neat aspect of the new dynamic array formulas is that you can reference the entire range of their results at once, for use in other formulas. Before, we were confined to either using static ranges, e.g. A1:A10, entire rows or columns, like A:A, or we had to define named ranges with complex formulas in their definitions in order to make them dynamic. Now, it couldn't be easier.

As I was saying at the end of the Dynamic Array Formulas post, Microsoft implemented a change that allows almost any formula to behave like an array formula, and to return an array of values if it receives an array as a reference:

Quick side note - this functionality can be denied and we can force the formulas to return to their previous functionality by using the @ operator in front of the function, for legacy compatibility:

To continue, though, with the subject of this article, we can use this new functionality in similar fashion to one of the uses of named ranges - to quickly reference their entire range of results in another location.

Let's say we want to find out which entries in the original list contain more than 5 characters - not easy to eyeball if the list is thousands of records long.

The # operator lets us reference the entire spill range of the formula in cell B1, regardless of its size. So if the values in the original range change, both the formula in cell B1 and our result in D1 will automatically change to reflect the new reality.

There are a bunch of unsightly zeros at the end of our spill range - and in other situations they could actually cause erroneous results. For example, if we wanted to know how many entries had fewer than 5 characters. They are there because we hardcoded a range into our LEN() formula, so it looks at it regardless of how our source range is changing. But we can change that by making the formula in B1 dynamic, the same way we used to do with named ranges:

What we did was change the range inside the LEN() formula from a static A1:A7 to a dynamic INDEX() formula which moves around as the length of our used range changes. The formula in D6 remained the same, and because it has B1# as its reference, it automatically reads the entire spill range of the formula in B1 without any further intervention.

Cat On A Spreadsheet

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet