Dynamic Arrays in Action

…and another thing about Dynamic Arrays.  You may recall we have previously 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.  But the times, they are a-changing: the brave new world of Office 365 (albeit selected Insider recipients for the time being), typing this formula would create a Spilled Array Formula.  To protect existing formulae / behaviour, Microsoft originally called upon a new function, SINGLE, to ensure what is known as “legacy array behaviour”.

Regular readers may recall the SINGLE function was born to ensure that Excel would keep Excel running smoothly.  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 falls in the intersection, then SINGLE would return a #VALUE! error.  When the supplied argument is an array, SINGLE returns the first item (the “top left-hand corner”, namely Row 1, Column 1).

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

Well, 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, if you 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 multi-cell 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: it’s necessary to prevent the new “spilled” behaviour.  Common functions that could return multi-cell ranges include INDEX, OFFSET and good ol’ 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 (e.g. SUM() or AVERAGE()). 

Microsoft has provided examples to try and 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 regards 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!!  You can check out more about Dynamic Arrays >here.