ICAEW.com works better with JavaScript enabled.
Exclusive

Dynamic Arrays – it's not just about the new functions and spilling

Author: Simon Hurst

Published: 03 Feb 2022

Exclusive content
Access to our exclusive resources is for specific groups of subscribers.

Perhaps the most obvious effect of the introduction of dynamic arrays in Excel is the 'spilling' of a cell containing a reference to a range of cells into as many adjacent cells as required.

In addition to this change, several new functions were introduced to take advantage of the new capability. However, at least equally important is the ability to process arrays within a formula in a single cell. This is not completely new. Before Dynamic Arrays, the Control+Shift+Enter key combination could be used to create an array formula, or the SUMPRODUCT() function could be used as a 'wrapper' for formulae that used arrays. For example, the following formula used to result in a #VALUE! error as the ROUND() function couldn't cope with a range of cells, rather than an individual value:

=ROUND($A$1:$A$3,0)

Since the introduction of Dynamic Arrays, this formula would spill down to the two cells beneath the actual formula cell to display three individual results. If your intention is to calculate the total of the rounded values, then you could try wrapping your ROUND() formula in a SUM() function:

=SUM(ROUND($A$1:$A$3,0))

Again, before Dynamic Arrays this would generate a #VALUE! error. To make it work, you would have to change it to an array formula, either by saving it using Control+Shift+Enter or by using SUMPRODUCT():

=SUMPRODUCT(ROUND(($A$1:$A$3,0))

Excel Screenshot

In our new era of Dynamic Arrays, we don't even really have to think about whether our formula includes an array or not, but instead just type our formula as 'normal':

=SUM(ROUND($A$1:$A$3,0))

Excel Screenshot

In effect, our Dynamic Array is spilled, and then recombined by SUM(), within our single cell.

Before we look at the advantages, it's worth pointing out that there are consequences for backward compatibility. If we were to use an earlier version of Excel to open a spreadsheet containing:

=SUM(ROUND($A$1:$A$3,0))

Excel would convert our formula to an array formula:

Excel Screenshot

One of the drawbacks of 'old' array formulae is that, unless you always save them using the Control+Shift+Enter key combination, they will revert to a normal formula when edited – even if no change is made.

The complexity and fragility of formulae that use Control+Shift+Enter has tended to how often they are used. Although using SUMPRODUCT() addresses the fragility concern, it does still add a layer of complexity and requires specific knowledge of what the function can do and how to use it. In a Dynamic Array world, people will be able to create formulae that use arrays without needing to worry about whether the function that they are using can cope with arrays or not.

Let's have a look at a practical application of this. One of my support clients recently asked if I could come up with a concise way to check whether a product code was in the correct format. They needed to identify any codes that were not in the format AAA999 where A is an upper-case alphabetical character and 9 is a number from 0 to 9. The number check is less of an issue as we can just use the ISNUMBER() function to check the three rightmost characters in the cell in one go:

=ISNUMBER(VALUE(RIGHT(A1,3)))

For the alphabetical test, it is less obvious how we can test without extracting each character and testing it individually. This would be a bit cumbersome, as we would need to create three separate formulae, each of which would need to check that the individual character was not 'before' the letter upper-case A or after the upper-case Z:

=AND(CODE(LEFT(A7,1))>=CODE("A"),CODE(LEFT(A7,1))<=CODE("Z"))

Excel Screenshot

To ensure that only upper-case, alphabetical characters pass our test, we have converted our characters to their corresponding ASCII codes using the CODE() function. We would need to test each of our three characters separately and combine the result using AND().

Using an array, we can come up with a formula that texts all three characters in one go, by using the MIN() and MAX() functions to check whether any one of the three characters is less than A or greater than Z:

=AND(MIN(CODE(MID(A1,{1,2,3},1)))>=CODE("A"),MAX(CODE(MID(A1,{1,2,3},1)))<=CODE("Z"))

We have adapted our single cell formula by using MID() rather than LEFT() and returning an array of the first three characters by setting the second argument of MID() – the starting position – to 1, 2 and 3. Note that to indicate we want to use a list of values, we enclose our 1,2,3 in braces by typing the braces (not by using Control+Shift+Enter).

In fact, thus far our formula will work in earlier versions of Excel, but it is inflexible: we have included the positions of the characters that we want to test, and the values to check against, within our formula. We can use arrays to make our solution much more generic. Here, we have created an Excel Table that lists the position of each of the characters that we want to check, together with the minimum and maximum values that each character has to fall within:
Excel screenshot

The main part of our validation formula just compares the ASCII code values of the character in each position indexed by the Position column against the ASCII code value of the corresponding characters in the Low and High columns:

=AND(CODE(MID(A10,tblMask[Position],1))>=CODE(tblMask[Low]),CODE(MID(A10,tblMask[Position],1))<=CODE(tblMask[High]))

There is a complication. If the number of characters in a cell is less than the maximum position in our list, then our formula will return an error, as the MID() function will be starting beyond the end of the available text. To avoid this, we will incorporate our check on the length of the code in an IF() statement that will ensure the rest of the check only takes place if the length of our code is equal to the highest value in our Position column:

=IF(LEN(A10)=MAX(tblMask[Position]),AND(CODE(MID(A10,tblMask[Position],1))>=CODE(tblMask[Low]),CODE(MID(A10,tblMask[Position],1))<=CODE(tblMask[High])),FALSE)

Although this example does demonstrate some useful techniques and concepts, I can't help feeling that there must be an easier way. If you know one (apart from using a database rather than a spreadsheet in the first place) please let us know at excel@icaew.com.