# Dynamic Arrays: 15 months later

byIt’s been 15 months since Microsoft rocked the calculation boat with its new range of dynamic array functions and features. But what’s actually changed since then? Our Excel MVP Liam Bastick takes a look.

If the responses to my LinkedIn account are anything to go by, half the world has been waiting for this. The long-awaited roll-out of dynamic arrays started recently. Microsoft announced at Ignite they will slowly become Generally Available from early November (ie a month ago!) with Excel for the web.

You are going to need Office 365 at some point (it's not coming to either Excel 2016 or Excel 2019), but Microsoft has stated that they "will be rolling out to subsets of our users to continue to gather feedback and monitor quality".

I have written about these at length, but in case you have just returned from Uranus, imagine a world of sorting data, identifying list items and filtering out what you don’t want formulaically with formulae that take three or four seconds to write.

The world has now realised that it won’t be coming to the perpetual licence versions such as Excel 2016 and Excel 2019. That wasn’t immediately clear, but users have come to realise this. It’s not so much that it has been in Preview for so long – we get that the calculations are revolutionary and there are still issues to work out such as:

- being able to have totals at the bottom of spilled arrays
- allowing spilled arrays to accept formatting
- having calculations generate the same results if calculated in a different order (see below).

It’s the fixation with Office 365 only that worries me. Whilst this helps the clamour for the subscription model, this may be a mistake. Modellers are going to be loathed to use functions and features that the majority of end-users will not be able to take advantage of. It may mean adoption will be slow for features people should be using in their millions. Similar judgment calls were made with Power Pivot, and eventually, Microsoft relented and made it available to all in Windows versions of Excel. I can’t help but think a similar perspective should be taken here.

Currently, we therefore have two types of Excel:

**DA Excel:**Excel that supports dynamic arrays, its functions and features**Legacy Excel:**the “traditional” Excel that is still wrapped in the world of**CTRL + SHIFT + ENTER**and does not support dynamic arrays.

This leads to compatibility issues.

### Compatibility issue #1: @ and SINGLE

You may recall the **SINGLE **function that was released last year.

In the past, if you entered **=A$1:A$10** anywhere in rows 1 through 10, the formula would return only the value from that row. In DA Excel, typing this formula would create a Spilled Array Formula. To protect existing formulae, the **SINGLE** function was created to return a single value using logic known as implicit intersection. **SINGLE** may return a value, single cell range or an error:

**=SINGLE(value)**.

For example, the two **SINGLE** formulae here are supplied a range, **H13:H27**, and return the values in cells **H17** and **H22** respectively, corresponding to the rows that were held in common:

Later, after its initial release, **SINGLE **was replaced with **@ **as follows:

Now, I mention this history with good reason. Excel will only remove **@ **from a formula where previous Excel versions would have used implicit intersection (as described above) to return a single value from a range, a named range or function parameter.

On the positive side, if you attempt to enter such a formula, Excel will warn you and do its utmost to stop you. It is still possible to cause an issue though. For example, in DA Excel, you could create the following formula:

In Legacy Excel, this would appear as:

Notice the error message is **=_xlfn.SINGLE(A2)**, not **[email protected](A2)**. This is confusing if you don’t know the history of the **@** operator. Worse comes if you try to evaluate this formula:

It generates an #NAME? error, which is far from ideal.

### Compatibility issue #2: Spill references

A formulaic reference to an entire dynamic array may use the spill reference suffix operator (that’s a mouthful), e.g.

The formula **=SUM(C2#)** sums the entire spilled range emanating from cell **C2**. Unfortunately, this is not converted as you might hope in Legacy Excel:

The formula in cell **G2 **is now **=SUM(_xlfn.ANCHORARRAY(C2)) **and, again, will not evaluate, viz.

### Compatibility issue #3: Uninvited CTRL + SHIFT + ENTER (CSE) array formulae

Take another look at that last example:

In DA Excel, the formulae in cells **C2:C6** contain the spilled formula **=A2:A6**. In Legacy Excel, these formulae have been replaced as follows:

The formula **{=A2:A6} **(created by using **CTRL + SHIFT + ENTER**) has been entered in each cell in cells **C2:C6**. This is because any formula that DA Excel thinks could return an an array (even if it is actually only returning a single value) is converted to a **CTRL + SHIFT + ENTER **(CSE) array formula in previous versions. This can impact memory and is creating unnecessarily complex formulae. This can cause several issues:

- a single vell containing a non-spilling formula is made unnecessarily complex and may not evaluate correctly
- a DA Excel dynamic array formula that is either spilling or blocked (#SPILL!) will be converted to a fixed CSE formula in earlier versions of Excel
- a formula entered in DA Excel as a multi-cell CSE formula may have backward compatibility issues.

You need to take great care when working with such instances.

### Another issue: Calculation order concern

There is another problem that is an issue in DA Excel. When I calculate something in Excel, if I use the same formula, I must get the same answer, right? Well – not necessarily. Consider the following:

In the example above, Calculations 1 and 2 are identical but deliver different results (ie different #SPILL! errors). Why?

- In Calculations 1 and 2, both values for Formula 1 and Formula 2 were originally set to 1. This causes no #SPILL! errors
- In Calculation 1, the value for Formula 2 (cell
**H13**) was then changed to 4 with no error - Then, in Calculation 1, the value for Formula 1 (cell
**H12**) was changed to 3. This caused the resultant #SPILL! error in cell**K16** - Next, in Calculation 2, the value for Formula 1 (cell
**H27**) was changed to 3 with no error - Then, in Calculation 2, the value for Formula 2 (cell
**H28**) was changed to 4. This caused the resultant #SPILL! error in cell**I33**.

I am not sure what the solution is for this problem. Technically, #SPILL! is working correctly, but it doesn’t seem right that two results may be generated in this instance depending upon what input I change first. The jury remains out on this one.

### Not all gloom and doom

**RANDARRAY **is one of the original functions in DA Excel. Because wasn’t Generally Available when it came out, it’s had a facelift this year. This is an important thing to note – this was the first time ever a function had been released publicly by Microsoft and then had its syntax changed. This is something that is possible to do before a function or feature becomes Generally Available – “Preview” means Microsoft reserves the right to change something as they see fit. That’s a good thing here.

Originally, the **RANDARRAY** function returned an array of random numbers between 0 and 1. However, there was a general sense of underwhelming with this function and the new and improved version has just been released. It now allows you to set you own maximum and minimum __and__ decide whether you want the values returned to be decimals (e.g. 17.4381672…) or integers (whole numbers).

The new syntax for the function is now as follows:

**=RANDARRAY([rows], [columns],[min],[max],[integer])**.

The function has five arguments, all supposedly optional (but upon testing, we weren’t quite as convinced):

**rows:**this specifies how many**rows**the results should spill over. If omitted, the default value is 1**columns:**this specifies how many**columns**the results should spill over. If omitted, the default value is also 1**min:**this is the minimum value that may be selected randomly. If this is not specified, it is assumed to be zero (0)**max:**this is the maximum value that may be selected randomly. If this is not specified, it is assumed to be 1**integer:**if this is set to TRUE, only integer outputs are allowed; the default value (FALSE) provides non-integer (decimal) results.

Other points to note:

- if
**rows**or**columns**refers to a blank cell reference, this will generate the new #CALC! error - if
**rows**or**columns**are entered as decimals, the values used will be truncated to the number before the decimal point (e.g. 3.9999999 will be treated as 3) - if
**rows**or**columns**is a value less than 1, #CALC! will be returned - if
**integer**is set to TRUE and either**min**or**max**is not an integer, this will generate an #VALUE! error **max**must be greater than or equal to**min**, else the error #VALUE! is returned.

When we originally discussed the **RANDARRAY **function, we used this rather comprehensive example to create a list of random integers between two values:

Originally, the formula in cell **F44** was

**=ROUNDDOWN(RANDARRAY(H36,H37)*(H39-H38+1),0)+INT(H38)**

and my past article explained how this worked. However, it’s much easier now:

The “new improved” formula in cell **F45 **(it’s moved down a row due to the additional argument required in cell **H40**) is simply

**=RANDARRAY(H36,H37,H38,H39,H40)**.

This is much simpler – and pretty cool.

### Impact of XLOOKUP and XMATCH

Since the announcement of dynamic arrays, there has been another major announcement of two new functions, **XLOOKUP** and **XMATCH**. At the time of writing, these two functions are both in Preview mode in Office 365 Insider Fast only.

**XLOOKUP **has the following syntax:

**XLOOKUP(lookup_value, lookup_vector, results_array, [match_mode], [search_mode])**

On first glance, it looks like it has too many arguments, but often you will only use the first three:

**lookup_value:**this is required and defines what value you want to look up**lookup_vector:**this reference is required and is the row or column of data you are referencing to look up**lookup_value****results_array:**this is where the corresponding item is you wish to return and is also required (even if it is the same as**lookup_vector**). This does__not__have to be a vector (ie one row or one column of cells): it may be an array (with at least two rows and at least two columns of cells). The only stipulation is that the number of rows/columns must equal the number of rows/columns in the column/row vector – but more on that later**match_mode:**this argument is optional. There are four choices:**0:**exact match (default)**-1:**exact match or else the largest value less than or equal to**lookup_value****1:**exact match or else smallest value greater than or equal to**lookup_value****2:**wildcard match. You should use the special character**?**to match any character and*****to match any run of characters.

What’s impressive, though, is that for certain selections of the final argument (**search_mode**), you __don’t__ need to put your data in alphanumerical order! As far as I am aware, this is a first for Excel

**search_mode:**this argument is also optional. There are again four choices:**1:**search first to last (default)**-1:**search last to first**2:**what is known as a binary search, first to last (requires**lookup_vector**to be sorted). Just so you know, a binary search is a search algorithm that finds the position of a target value within a sorted array. A binary search compares the target value to the middle element of the array. If they are not equal, the half in which the target cannot lie is eliminated and the search continues on the remaining half, again taking the middle element to compare to the target value, and repeating this until the target value is found**-2:**another binary search, this time last to first (and again, this requires**lookup_vector**to be sorted).

**XLOOKUP **is a powerful substitute for both **VLOOKUP **and **INDEX MATCH**:

You can clearly see the **XLOOKUP** function is shorter:

**=XLOOKUP(H52,F41:F47,G41:G47)**

Only the first three arguments are needed, whereas **VLOOKUP** requires both a fourth argument, and, for full flexibility, the **COLUMNS** function as well. **XLOOKUP **will automatically update if rows/columns are inserted or deleted. It’s just simpler.

Further, the sister function **XMATCH** has the following syntax:

**XMATCH(lookup_value, lookup_vector, [match_mode], [search_mode])**

where:

**lookup_value:**this is required and defines what value you want to look up**lookup_vector:**this reference is required and is the row or column of data you are referencing to look up**lookup_value****match_mode:**this argument is optional. There are four choices:**0:**exact match (default)**-1:**exact match or else the largest value less than or equal to**lookup_value****1:**exact match or else smallest value greater than or equal to**lookup_value****2:**wildcard match. You should use the special character**?**to match any character and*****to match any run of characters.

Again, for certain selections of the final argument (**search_mode**), you __don’t__ need to put your data in alphanumerical order

**search_mode:**this argument is also optional. There are again four choices:**1:**search first to last (default)**-1:**search last to first**2:**this is a binary search, first to last (requires**lookup_vector**to be sorted)**-2:**another binary search, this time last to first (and again, this requires**lookup_vector**to be sorted).

As you can see, it’s a fairly straightforward addition to the **MATCH **family. It acts similarly to **MATCH **– just with heaps more functionality.

It’s clear to see both of these functions would work well with dynamic arrays and spilled references. Since the announcement of these two formulae, people have been knocking down the door to use them in a Generally Available environment.

### Word to the wise

If you have Office 365 and you don’t have dynamic arrays yet, don’t worry, be patient: it’s coming, but you may use it in Excel on the Web in the meantime. If you are on a perpetual licence (e.g. Office 2016 or Office 2019), this might be the thing that forces your hand to move to the subscription model. We’ll wait and see.

*Liam Bastick is author of an Introduction to Financial Modelling, which provides a simple walkthrough of the common perils and pitfalls of financial modelling. Presently, this book is only available on Amazon Australia but if anyone would like to buy a copy directly contact Liam via this link. AccountingWEB readers receive a 10% discount plus p&p (including tracking and insurance at cost).*

### You might also be interested in

Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...