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 braces {}): This is done to ensure previous versions of Excel will not trigger implicit intersection.

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! error.
• 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! 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, 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.