# Microsoft makes major Dynamic Arrays modifications

Excel’s new Dynamic Arrays functionality has not been out long — and it’s not available to most either — but Microsoft has already elected to make some significant changes.

The decision reduces the number of new Dynamic Array-related functions from seven to six. That’s because Microsoft has replaced the **SINGLE** function with the **@ **symbol. Microsoft also has expanded the capabilities, and streamlined the operation, of the **RANDARRAY **function.

Most Excel users don’t yet have access to Dynamic Arrays because they exist in what Microsoft refers to as “Preview” mode, ie,* *they are not yet “Generally Available”. However, the change with the **SINGLE** function does have ramifications for current Excel users.

SINGLE’s departure is where it’s @

My article “Excel: 2 New Errors and a SINGLE Function” discussed the concept of **implicit intersections**. In the past, if you entered **=A1:A10** anywhere in rows 1 through 10, the formula would return only the value from that row. With Dynamic Arrays, typing this formula would create a spilled array formula. To protect existing formulas/behaviour, Microsoft originally called upon a new function, **SINGLE**, to ensure what is known as “legacy array behaviour”. The **SINGLE** function returned a single value using this implicit intersection logic.

In the initial release of Dynamic Arrays to Office 365 Insider Fast, when **SINGLE **referred to a range, this function would return the cell at the intersection of the row or column of the formula cell. Where there was no intersection, or more than one cell fell in the intersection, then **SINGLE** would return a **#VALUE!** error. When the supplied argument is an array, **SINGLE** would return the first item (the “top left-hand corner”, namely Row 1, Column 1).

For example, originally, the two **SINGLE** formulas in the example pictured below are supplied a range, **H13:H27**, and return the values in cells **H17** and **H22**, respectively.

Now it’s all changed. Ladies and gentlemen, let me introduce you to the new implicit intersection operator, **@**. That’s right — Microsoft has changed its great collective mind and kicked **SINGLE **to the kerb for the more succinct **@ **operator.

It’s true that the **SINGLE **function was causing confusion and **@ **seems less obtrusive. But there was more to it than that. Since Excel 2010, the **@** symbol has already been employed in Table references to indicate implicit intersection (and it was used there to replace previous Excel syntax too!).

For example, consider the Table formula **=[@Column1]**. Here, the **@** indicates that the formula should use implicit intersection to retrieve the value on the same row from **[Column1]**.

Going forward then, **@ **will replace **SINGLE**, and functions that return multicell ranges or arrays will be prefixed (“prepended” in Microsoft-speak) with **@** if they were authored in an older version of Excel.

It is important to note that there is no change to the way your previous formula behaves — that’s the entire point of Microsoft’s move: It’s necessary to prevent the new “spilled” behaviour. Common functions that could return multicell ranges include **INDEX**, **OFFSET**, and good old user-defined functions (UDFs). It should be noted, though, that a common exception is if they are wrapped in a function — often an aggregate one — that accepts an array or range (eg, **SUM()** or **AVERAGE()**).

Microsoft has provided examples to try to clarify how things should work:

Remembering that the **@ **symbol will only appear in “Dynamic Array Excel”, do consider carefully the ramifications of removing these pesky little **@ **critters. Before deleting, check the function/formula immediately after the **@ **symbol. With regard to this expression:

- If it returns a single value (the most common case), there will be no change by removing the
**@**symbol. - If it returns a range or an array, removing the
**@**will cause the formula to spill into the neighbouring cells. - If you remove the
**@**and later open the workbook in an older version of Excel, it will appear as a legacy array formula (wrapped with the Ctrl+Shift+Enter

You have been warned. Don’t put your spreadsheets **@** risk!!

Microsoft simplifies RANDARRAY

We first mentioned **RANDARRAY **back in our series of articles on Dynamic Arrays. Originally, the **RANDARRAY** function returned an array of random numbers between 0 and 1. This function has been revamped: It now allows you to set your own maximum and minimum *and* decide whether you want the values returned to be decimals (eg,* *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, I’m not 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 noninteger (decimal) results.

If you are lucky enough to get access to the new RANDARRAY, do be aware of the following:

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

When we originally discussed the **RANDARRAY **function, I 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 I explained at length 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).**

See how simple that is? If this keeps up, I’m going to be redundant soon!

The Insider scoop

if you want to see if you can gain access to Excel’s preview mode, you may register in **File -> Account -> Office Insider **in Excel’s backstage area:

Whilst you’re not guaranteed access, as only some users will receive the new features as Microsoft slowly rolls them out, please don’t let that put you off. These features will be with all Office 365 subscribers soon.

You can check out more about Dynamic Arrays in our seven-part series that ran in late 2018. Links are below.

**Getting started**

Part 1: Excel: Microsoft Unveils Dynamic Upgrades

Part 2: Excel: Arrays, Tables, and ‘The Twilight Zone’

Part 3: Excel: 2 New Errors and a SINGLE Function

**Function focus**

Part 4: This Excel Change is More Than SORT of a Big Deal

Part 5: Focus on 2 New Excel Functions: UNIQUE and FILTER

Part 6: Excel New Function Focus: SEQUENCE and RANDARRAY

**Final thoughts**

Part 7: Will Dynamic Arrays Kill Data Tables, PivotTables?

*— Liam Bastick**, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of *An Introduction to Financial Modelling*. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an *FM* magazine senior editor, at Jeff.Drew@aicpa-cima.com.*