Excel Dynamic Array Improvements
Published Feb 19 2019 11:27 PM 44.9K Views
Microsoft

Since announcing dynamic arrays, we’ve received an overwhelmingly positive response from the community. Being a fundamental change to the way formulas work, we wanted to take some time to gather and incorporate user feedback. Below are some of the improvements we've made.

 

Dynamic Arrays in ActionDynamic Arrays in Action

 

Introducing the Implicit Intersection Operator: 

Regular Excel formulas had a calculation behavior called implicit intersection that would silently select a single value from an array of values. This ensured formulas would always return just one value as that's all a cell could hold.

 

Implicit intersection worked as follows: 

  • If the value was a single item, then return the item
  • If the value was a range, then pick the cell on the same row or column as the formula
  • If the value was an array, then pick the top left value

With the advent of dynamic arrays, Excel is no longer limited to returning single values from formulas, so invisible implicit intersection is no longer needed. Where an old Excel formula could invisibly trigger implicit intersection, dynamic array enabled Excel shows where it would have occurred. With the initial release of dynamic arrays, Excel indicated where this occurred by using the SINGLE function. However, based on user feedback, we’ve moved to a more succinct notation: the @ operator.  

 

Why did we select the @ symbol? The @ symbol is already used in table references to indicate implicit intersection. Consider the following formula in a table =[@Column1]. Here the @ indicates that the formula should use implicit intersection to retrieve the value on the same row from [Column1].  

 

When should you expect to see the @? Generally speaking, functions that return multi-cell ranges or arrays will be prefixed with @ if they were authored in an older version of Excel. It's important to note that there is no change to the way your formula behaves, you can just see the previously invisible implicit intersection. Common functions that could return multi-cell ranges include INDEX, OFFSET, and User Defined Functions (UDFs).  A full list can be found here. A common exception is if they are wrapped in a function that accepts an array or range (e.g. SUM() or AVERAGE()). 

 

Let’s consider some examples: 

 

Original Formula  

As seen in Dynamic Array Excel 

Explanation 

=SUM(A1:A10) 

=SUM(A1:A10) 

No change - No implicit intersection could occur, as the SUM function expects ranges or arrays. 

=A1+A2 

=A1+A2 

No change - No implicit intersection could occur. 

=A1:A10 

=@A1:A10 

Implicit intersection will occur 

=INDEX(A1:A10,B1) 

=@INDEX(A1:A10,B1) 

Implicit intersection could occur. The INDEX function can return an array or range when its second or third argument is 0.  

=OFFSET(A1:A2,1,1) 

=@OFFSET(A1:A2,1,1) 

Implicit intersection could occur. The OFFSET function can return a multi-cell range. When it does, implicit intersection would be triggered. 

=MYUDF() 

=@MYUDF() 

Implicit intersection could occur. User Defined Functions can return arrays. When they do, the original formula would have triggered implicit intersection. 

 

Can you safely remove the @? It depends on what the part of the formula to the right of the @ returns: 

  • If it returns a single value (the most common case), there will be no change by removing the @
  • If it returns a range or array, removing the @ will cause it to spill to the neighboring 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 braces {}), this is done to ensure old Excel will not trigger implicit intersection. 

 

Object Model (VBA/VSTO) 

We’ve made some tweaks to the object model to improve compatibility with existing add-ins and macros. We’ll be introducing Range.Formula2 which will supersede the existing Range.Formula. Setting one will automatically set the other.

 

Range.Formula will continue to be supported and will i) be silent on implicit intersection when read, and ii) implicitly intersect as required when set. Setting Range.Formula will always result in a formula that will never spill, because implicit intersection will force it to return just one result. This is consistent with how it behaved before the introduction of dynamic arrays.

 

Range.Formula2 is what will be reported in dynamic array Excel’s formula bar. Any implicit intersection operations will be indicated by the @ operator.  

 

When should I use Range.Formula vs Range.Formula2? If you expect your code/macro to only run in dynamic array versions of Excel, you should simply use Range.Formula2. If, however, you want to target all versions of Excel you should use Range.Formula, but you should understand the differences.  

 

Here is a short VBA example to illustrate some differences: 

 

[B1].Formula = "=A1:A10" 'The value of A1 is shown in B1 and no spill occurs
MsgBox [B1].Formula 'The message box shows =A1:A10
MsgBox [B1].Formula2 'The message box shows =@A1:A10 consistent with the formula bar

 

To improve compatibility for macros recorded in dynamic array enabled Excel when opened in older versions of Excel, the Excel Macro Recorder will record Range.Formula when it would result in the same formula as Range.Formula2.   

 

Object Model (JS) 

In JavaScript, we’ll be updating Range.Formula to continue reporting the contents of the formula bar rather than introducing Range.Formula2. The reasons for the variance vs VBA/VSTO is i) it simplifies the JS object model, ii) the vast majority of Office JS addins already behave correctly in dynamic array enabled Excel, iii) it’s possible for developers to centrally deploy updates to Office JS Addins (if required) and iv) Office JS addins are newer and, in most cases, are being actively developed and maintained. 

 

Availability notes: 

We’ll be rolling out these changes to users signed up for the Office 365 Insiders Program starting today. Over the coming months, we’ll continue gathering feedback and monitoring feature quality as we move towards making the feature available to all Office 365 users. 

 

Joe McDaid (@jjmcdaid) 

Program Manager, Excel 

71 Comments
Copper Contributor

Over the coming months? What happened to Q1 of 2019? We've been waiting on these since September of last year.

Steel Contributor

@SWill856 - this is a HUGE change to the Excel calc engine. I'd rather they take their time to get it right than rush it out based on an arbitrary date.

 

Will the new builds automatically find and replace any existing SINGLE() functions with @ as files are opened? I see SINGLE() is still available as a function in current insider builds. Will it be removed?

 

@JoeMcDaid - in the example worksheet I sent you last week, this build is still "corrupting" some formulas. I restored that file to a pre-Dynamic Array version of the worksheet to start clean and opened on the Monthly Targeted build. So far so good.

 

=IFERROR(
    IFS(
        ISBLANK([@[Item No.]]),"",
        COUNTIFS([Bin],[@Bin],[Item No.],[@[Item No.]])>1,"Error - Duplicate Bin/Item Combo",
        NOT(ISBLANK([@[Item No.]])),INDEX(tblItemMaster[item_desc_1],MATCH([@[Item No.]],tblItemMaster[item_no],0))
    ),"Error - Invalid Item Number - Check Tag")

Became below when I opened it in the Insider build.

 

 

=IFERROR(@
    IFS(
        ISBLANK([@[Item No.]]),"",
        COUNTIFS([Bin],[@Bin],[Item No.],[@[Item No.]])>1,"Error - Duplicate Bin/Item Combo",
        NOT(ISBLANK([@[Item No.]])),INDEX(tblItemMaster[item_desc_1],MATCH([@[Item No.]],tblItemMaster[item_no],0))
    ),"Error - Invalid Item Number - Check Tag")

with the @ added on the first row. Saving, closing, and opening that in a Monthly Targeted build worked ok. The @ got removed.

 

 

I then edited on this weeks Insider build, adding the &"test" to the last row and saved/closed it.

 

 

=IFERROR(@
    IFS(
        ISBLANK([@[Item No.]]),"",
        COUNTIFS([Bin],[@Bin],[Item No.],[@[Item No.]])>1,"Error - Duplicate Bin/Item Combo",
        NOT(ISBLANK([@[Item No.]])),INDEX(tblItemMaster[item_desc_1],MATCH([@[Item No.]],tblItemMaster[item_no],0))
    )&"test","Error - Invalid Item Number - Check Tag")

When I opened it on a Monthly Targeted build, 1902, build 11328.20070 it did the _xlfn.SINGLE() thing again.

 

=IFERROR(_xlfn.SINGLE(
    IFS(
        ISBLANK([@[Item No.]]),"",
        COUNTIFS([Bin],[@Bin],[Item No.],[@[Item No.]])>1,"Error - Duplicate Bin/Item Combo",
        NOT(ISBLANK([@[Item No.]])),INDEX(tblItemMaster[item_desc_1],MATCH([@[Item No.]],tblItemMaster[item_no],0))
    ))&"test","Error - Invalid Item Number - Check Tag")

Now the entire cell is useless on anything but an Insider build. Now to start over.

 

So I cleaned all of that up and got it as the function at the top again, then I decided to open it on both PCs at the same time - hosted in Sharepoint online.

 

I first edited the function on the Monthly Targeted (MT) build and both MT and Insider showed the formula as the first one above - no @, no SINGLE(), no nothing. Then after I confirmed the MT edit was recognized by the Insider build I edited it on the Insider build, tweaking my "test" text. That caused the MT build to say the file had been modified but it couldn't open it, so I needed to either Save As, or discard and reopen. I chose the latter, and the MT build then showed this CSE function. ¯\_(ツ)_/¯

 

={IFERROR(
    IFS(
        ISBLANK([@[Item No.]]),"",
        COUNTIFS([Bin],[@Bin],[Item No.],[@[Item No.]])>1,"Error - Duplicate Bin/Item Combo",
        NOT(ISBLANK([@[Item No.]])),INDEX(tblItemMaster[item_desc_1],MATCH([@[Item No.]],tblItemMaster[item_no],0))
    )&"test","Error - Invalid Item Number - Check Tag")}

 

 

 

Copper Contributor

@Ed Hansberry I don't want them to rush either. I just want them to do a better job of managing expectations. People have been expecting these to roll out any day now, based on what Microsoft told them last year. They should have released some kind of announcement or update when they realized the original timeline was unfeasible, not mention it offhandedly in a blog post after the original timeline has almost elapsed . All I'm asking for is a little communication with the users.

Copper Contributor

Yes, I agree with @SWill856 . This should be expedited. We are eagerly waiting since months and  expecting it to be rolled out sometime in Mar'19.

It seems... that is not to be.

Copper Contributor

Hi @JoeMcDaid, thanks for sharing this Excel Formula.

Copper Contributor

Kind of cool, but also kind of concerning. I'd rather that they flip it so that the single cell answer is the default and the multi cell one is the exception with the @. That way would be better for experienced users who know what they want and how to structure the data. Their proposal is better for casual users who just put in formulas without knowing what they're going to get.

 

It will encourage sloppy, spread out spreadsheet design to accommodate all of the arrays that will be returned by these formulas.
 
Also I'm sure that my clients will have a bunch of messed up spreadsheets where the user thought they were getting a 1 cell answer, but they overwrote part of their model. OR where an array extends past where the range they set in a formula somewhere else, and the data gets excluded.
Steel Contributor

@AltIR - results returned by dynamic arrays won't be incomplete or overwrite data. It will stop and return #SPILL so you know it has more data than can be shown in the available cells before it hits other data. And when referencing results of an array, you reference it with a # so the range dynamically expands. =#c1 for example, would return c1:z1000 if an array returned something that large. Or c1:z5 if it returned a smaller array the next time it calculated.

Copper Contributor

@Ed Hansberry 

 

Thanks for the response; that's very helpful information! I suppose I'm not concerned that there will be tools to handle those issues, rather I'm questioning what the default setting should be in order to prevent casual users from creating problems.

 

For example at the moment most of my clients don't know how to reference named ranges or table objects, and that works because they don't have dynamic arrays moving around without their knowing. If the dynamic array becomes the default setting, then I think they will have problems because those users (most users) will never learn to use the # sign to reference the array. Instead, they will compensate by changing the structure of their work or by simply having more errors.

 

 

Steel Contributor

Well, understand that most formulas won't generate dynamic arrays. If your clients aren't that sophisticated and are using SUM() and not much else, they will never hit this. But they will know something is up when they enter a formula that does trigger a dynamic array and it creates an answer that spans multiple cells.

 

I do agree though, it will be interesting to see how some people respond as this happens. But honestly I think it is for the best. Previously doing something like =SUM(F10:F19*G10:G19) would return an error unless entered CSE, and should have been SUMPRODUCT(). But now it will just work.

Microsoft

@Ed Hansberry Thanks for taking the time to provide a really detailed repro! The engineers are looking into it.

 

@ppani270 @SWill856 Great to see the excitement for the feature! We cannot wait to get it out to all Excel 365 users. However, before we do, we need to make sure it meets our high quality bar and this takes time. Please note that we have not announced any expected availability dates, though some Excel bloggers may have made their own unofficial predictions which might be the cause of the mix-up.

Steel Contributor

Also eagerly awaiting the release date and i like the new @ operator - it is reminiscent of the Lotus 123 era :)

Re: the list of functions returning range or array. I wonder if HLOOKUP and VLOOKUP should be included in this list? My understanding, based on results shown in the function wizard, is that these functions were written to return an array of results when an array or range reference was supplied as third argument. This would make sense from an efficiency standpoint as the lookup operation would then only needed to be carried out once when returning multiple results. In the latest insider build, for example, setting A1 equal to 1, the following formula returns 1 entered normally but 64 with CSE:

 

=TYPE(VLOOKUP(A1,A1,A1))

Steel Contributor
Any ideas when the new @ functionality will be coming to the Mac version? Updated today and it is the old SINGLE() function still.
Copper Contributor

I think they should change the Default behavior as below

=A1 then Enter - Same as before

=A1:A10 then Enter - same as before (no spill) - Implicit intersection

=@A1:A10 and then Enter - Spill

 

Steel Contributor

So I just discovered =UNIQUE(table[field]) always returns the same results, regardless of whether or not there is a filter set on that field. Smiley Sad

 

Does anyone know if it is possible to use FILTER with some sort of ISVISIBLE() type of criteria? 

Copper Contributor

@ed

If D is the name of a Table and Name is a Field in the table then the below formula can be wrapped inside UNIQUE to get a list of Unique "Filtered" items

 

=UNIQUE(INDEX(D[[#All],[Name]],MODE.MULT(IF(SUBTOTAL(3,OFFSET(D[[#Headers],[Name]],ROW(D[Name])-1,0)),ROW(D[Name])*{1,1}))))

 

 

Silver Contributor

@lori_m 

The formulae

= TYPE(H4#)

= TYPE(result#)

= TYPE(VLOOKUP( value, array, 2 ))

all return 64, whereas

= TYPE(H4)

= TYPE(result)

return 1.

 

The 'value' I sought was a 2x2 range giving an array of values

{"ddd","aaa";"bbb","fff"}

with the formula anchored in cell H4 and named 'result'.

 

Steel Contributor

Thanks very much @sameer bhide. Works perfectly. Now I have to dissect it and figure out what the heck it is doing. :)

Silver Contributor

@Ed Hansberry 

If you are willing to introduce a helper field 'test'

= SUBTOTAL(103,@string)

then

= UNIQUE(FILTER(string, test))

filters out the hidden rows before building the list of unique values.

  

@AltIR 

I wouldn't worry too much about your 'casual' users.  If they don't know enough to put two cells together they are unlikely to get an spilt array; it's a bit like trying to create fire by rubbing one stick together :) 

Steel Contributor

@Peter Bartholomew

As I see it behaviour isn't quite consistent between versions. For example i'd expect this to spill:

 

=VLOOKUP({1},{1},{1,1})

 

The insert function dialog shows an array return here but wrapping in SUM() only returns 1. It'd be preferable if VLOOKUP could process arrays over first and third arguments together (especially in light of the recent performance improvements) which would presumably entail returning scalars only like for most other functions.

Steel Contributor

I understand that approach @Peter Bartholomew but I cannot add columns. This is the result of a Power Query process that gets exported to CSV. So the extra column would have to be cleaned up, and would also be replicate over 200,000 times when it is being used.

 

So I could add it, but it isn't practical in this instance.

Silver Contributor

@lori_m 

 

Maybe it is due time for VLOOKUP to go on the scrapheap!  Various combinations of 

= SUM( INDEX( {1}, MATCH( {1;1}, {1} ), {1,1} ) )

seem to work as may be expected.

 

VLOOKUP appears to switch from multiple columns for a single lookup value to returning the first column for multiple lookup values.

Microsoft

@lori_m thanks for the heads up on =VLOOKUP({1},{1},{1,1}) not spilling. We are investigating.

 

@sameer bhide nifty!

@Ed Hansberry , @sameer bhide , another version could be

=UNIQUE(FILTER(D[Name],(SUBTOTAL(3, OFFSET(D[Name], MATCH(ROW(D[Name]), ROW(D[Name]))-1, 0, 1))>0)*(ROW(D[Name])-ROW(D[[#Headers],[Name]]))>0))

 

Steel Contributor
@Sergei Baklan nice, and i think that maybe could be further simplified:
=UNIQUE(FILTER(D[Name],SUBTOTAL(3,OFFSET(D[Name],SEQUENCE(ROWS(D[Name]),,0),0,1))))
 
if data is numeric, a non-volatile version that orders results might be:
=UNIQUE(AGGREGATE(15,7,D[Name],SEQUENCE(AGGREGATE(2,7,D[Name]))))

@lori_m  - yes, great! Where ROWS that could be SEQUENCE. where MULT - FILTER. Maybe not...

Silver Contributor

For non-numeric data I came up with something similar to Lori but, again, I required a helper range of 

= SEQUENCE( ROWS(List) )

which could be in any column but needs to be aligned row-wise with the data table.  The filtered index# column could then be used to look up the required entries from the list

= UNIQUE( INDEX( List, AGGREGATE( 15, 7, index#, SEQUENCE( SUBTOTAL(103, List) ) ) ) )

I realise I wasn't allowed a helper range but a self-dimension index in some distant column may not be too intrusive.

Copper Contributor

@lori_m 

I think we can do better

=UNIQUE(FILTER(D[Name],SUBTOTAL(3,OFFSET(D[[#Headers],[Name]],ROW(D[Name])-1,0))))

 

sam

@sameer bhide , when it shall be

=UNIQUE(FILTER(D[Name],SUBTOTAL(3,OFFSET(D[[#Headers],[Name]],ROW(D[Name])-ROW(D[[#Headers],[Name]]),0))))

SEQUENCE doesn't care where is the table located

 

Silver Contributor

@Sergei Baklan 

I am not sure I understand the point you were making

"SEQUENCE doesn't care where is the table located"

(though I would accept it as a true statement).

 

For me, the following

= UNIQUE( FILTER( D[Name], SUBTOTAL( 3, OFFSET(D[#Headers],SEQUENCE(ROWS(D)),0) ) ) )

worked well and is more in keeping with the spirit of array formulas in which it is position within the array that counts, not position on the worksheet.

Steel Contributor

@lori_m - thanks! Your's is the shortest. Winner! :)

@Sergei Baklan & @sameer bhide - thanks both for yours as well. All are excellent tools for learning. Your last one @sameer bhide though doesn't work for me. In alpha data, it just returns a unique list of the data, filtered or not.

 

=UNIQUE(FILTER(D[Name],SUBTOTAL(3,OFFSET(D[[#Headers],[Name]],ROW(D[Name])-1,0))))

@Ed Hansberry , let me summarize a bit. All latest formula have exactly the same logic if ignore the difference do we offset from header of first row of the table

=UNIQUE(FILTER(D[Name],SUBTOTAL(3,OFFSET(D[Name], <array {0..TableSize} generator>  ,0,1))))

Of course, most natural for new Excel 

=UNIQUE(FILTER(D[Name],SUBTOTAL(3,OFFSET(D[Name], SEQUENCE(ROWS(D[Name]),,0)  ,0,1))))

If we take into account table location traditional ROW() also works with

=UNIQUE(FILTER(D[Name],SUBTOTAL(3,OFFSET(D[Name], ROW(D[Name])-ROW(D[[#Headers],[Name]])-1 ,0,1))))

Another way to ignore offseting of the table location

=UNIQUE(FILTER(D[Name],SUBTOTAL(3,OFFSET(D[Name], MATCH(ROW(D[Name]),ROW(D[Name]),0)-1  ,0,1))))

But again, the logic of all formulas is the same. 

 

 

 

 

Silver Contributor

Returning to the original topic, I like the implicit intersection operator.

 

One thing I would like to query, though, is the fact that it is limited to returning a single value.  In tradition Excel, that was an intentional device to limit the result of each formula evaluation to be a scalar.  Now that a cell can hold an array result, isn't it somewhat perverse to limit '@' in this way.

Broadly, the operator could return the same array as

= ((R,C) name#) 

allowing, for example, a row sum

= SUM(@name#)

where 'name' is the anchor cell of a 2D spilt array or a similar-sized range.

 

At first sight this would appear to violate the main purpose of the operator, namely to provide backward compatibility.  But then again, is it absolutely essential that modern Excel should be capable of returning the same error?  The developer should have corrected it.

 

I would also observe that the table operator Table1[@] returns an entire record.

Copper Contributor

@JoeMcDaid 

 

Hey Joe, thanks for the update.  I've been eagerly awaiting this since last Fall and have been trying to get more details on the release date.  My org only does Monthly Targeted and not the faster Insider's release.

 

I just checked the Office 365 roadmap though and don't see any guidance on timing there:

https://www.microsoft.com/en-us/microsoft-365/roadmap?filters=Excel

 

I could swear when I checked several weeks back there was at least an 'in development' entry on this feature with expected release in 2Q19.  How come this feature isn't shown on the roadmap?  Can you guys provide more specific guidance on release timing?

Overall this is a critical feature that's long overdue (your Mountain View competitor has had this for years...) since it so dramatically simplifies array formulas and opens the door to an entirely new working model.  It would be great to have a bit more visibility into when I can get my hands on this.

 

Thanks,

James

 

Steel Contributor

One (likely unintended) application of the @ operator is as a worksheet version of the CALL macro function. As an example enter from the VBE immediate window:

 

[a1]=ExecuteExcel4Macro("register(""msvcrt"",""_strrev"",""cc"")")

 

To return the contents of cell B1 in reverse order, enter in another cell:

 

=(@+A1)(B1)

 

Normally you'd just assign a function name like STRREV, but here function is a formula input instead - for instance for choosing among various hash functions.

Steel Contributor
Actually =(@+A1)(B1) is now crashing for me on latest build :(
Better to use the following alternative which works in all versions:
=IF(1,+A1)(B1)
Silver Contributor

@lori_m 

Forgive me for querying your post but I understand neither the syntax nor the intent of your formulas.  Something is clearly going on because #VALUE! errors can turn into #REF! errors or crash Excel but I have no idea where it is going.

Steel Contributor

@Peter Bartholomew: To be more explicit, we can call a DLL function in the normal way from the sheet by first registering the function using:

ExecuteExcel4Macro "register(""msvcrt"",""_strrev"",""cc"",""STRREV"")"

 

and then call it from the sheet with:

=STRREV("hello")

 

If we select the 'STRREV' portion of this formula and press F9 we get the register id of the function (-996147200.) and an ' @' symbol:

=(@-996147200)("hello")

We can then try to apply this method to a list of registered functions in the first column, along with arguments in subsequent by filling down a simple formula. 

 

But if A1 = -996147200 then =(@A1)("hello") doesn't work and other alternatives seem to crash in current versions. On the other hand: =IF(1,+A1)(B1) does work as expected and could be filled down as required. 

 

Silver Contributor

@lori_m 

Thanks for the explanation; not something I would have stumbled on by accident!

Sometimes it is the occurrence of an error that is the puzzle.  One situation I remember is trying the mathematically transparent

= ColumnArray( 3 )

rather than the more opaque spreadsheet approach

= INDEX( ColumnArray, 3 ).

That it fails is no surprise but why with a #REF! error?

Steel Contributor

Yes, interesting. Perhaps the #REF! error is associated with the function call operator (.) passing the function "by reference" like other reference operators ' : [ ] , # @ ' ?

 

Name definitions that support arguments in this way include '=Macro1!A1', '=myUDF' or '=register_id' but not eg '={1,2,3}'. '@' could support arguments too - if stability issues get sorted :)

Copper Contributor

The major use of the implicit intersection is Named Ranges, so I am surprised it is not discussed.

 

E.g. if

FOO=$A$1:$C$1 and

BAR=$A$2:$C$2

then if we put the formula

= FOO + BAR

in, say, B3, then it will add B1 to B2.

We might then copy that formula from A3 to C3.

 

So this will produce lots of @ signs.

 

But what is the way forward?  Do we continue to copy formulas?  Or do we just put the one formula without @ in A3 and let it spill?

 

This is a HUGE change to the way Excel works.  No longer copying formulas.

 

And now if we look into cell B2, say, how do we figure out how the value got into that cell?.

Silver Contributor

@Anthony Berglas 

"Do we continue to copy formulas?"  No

"Or do we just put the one formula without @ in A3 and let it spill?" Yes

"This is a HUGE change to the way Excel works.  No longer copying formulas." Thank God (or at least @JoeMcDaid  and his team)

[ Note: A major role of auditing software is to identify copying errors and overwritten cells -- no longer needed! ]

 

"And now if we look into cell B3, say, how do we figure out how the value got into that cell?"

Who cares?  If

= FOO + BAR

is a correct formula applied to the array as a whole, the chance of INDEX( FOO+BAR, 2 ) being wrong is negligable. 

 

Further notes: Naming the output $A$3:$C$3 presents an interesting challenge.

One way is to introduce the name FOOBAR that refers to =$A$3 in with case you have downstream formula in the form

= FOOBAR# - AVERAGE(FOOBAR#)

or you define it to refer to =$A$3#, in which case the downstream formulas are of the form

= FOOBAR - AVERAGE(FOOBAR)

 

The former makes it easier to locate the name that the developer has applied to a dynamic range but the latter causes less disruption if the formula is later uploaded to the name 'Refers to' box.  It avoids references to FOOBAR# being turned into an error which has to be corrected by deleting the redundant "#" in each formula.

 

@JoeMcDaidCould a redundant "#" appended to a name that is already a spilt range simply be ignored?  It would allow me to prepare a named formula using worksheet cells and then upload to the name without issue.

Copper Contributor

Hi 

Has anyone found the roadmap item or release date for this yet please?

@Xerxel , official roadmap is here https://www.microsoft.com/en-us/microsoft-365/roadmap?filters=Excel, it has nothing about DA

Copper Contributor

No update yet as to when regular Office 365 for Business users will get this feature?

Steel Contributor

@Mike123456 - The week of July 15th Monthly Targeted got it, so it is now outside of just Insiders. No idea how long it will say in Monthly Targeted channel before going to Monthly. Could be one month, or six more. Whenever it does go to Monthly, it will likely be at least 6 more months before it hits the deferred channel since that just gets 2 updates a year.

@Ed Hansberry , I have DA on Monthly Targeted from the end of June, and I'm almost sure not all Insiders Slow have DA now.

Steel Contributor

According to this link @Sergei Baklan it is released. Nothing about a staged rollout. I'm not saying everyone with Monthly Targeted has it, but according to this documentation, they should have it, or MS needs to clarify if there is a staged rollout happening.

Release notes.

@Ed Hansberry , it's great if so. However, usually it takes time after the announcement to cover all channel users. 

Steel Contributor

Has anyone figured out a way to do what effectively a GROUPBY() function would do?

For example, in this data:

Item          Sales

A1
B1
C1
A2
B3
C4

 

It is easy to get a unique list of items: =UNIQUE(SampleData[Item])

 

But I cannot figure out how to dynamically get a subtotal of the items to return a table that would look like this:

ItemSales
A3
B4
C5


I know I can use the SUMIFS() formula in the Sales column easily, but that will not dynamically expand. If I add an item "D" in the original table, the UNIQUE() function will grab the info for the Item column, but the Sales column will be missing the total until I copy the SUMIFS() down.

 

I know the Sales column would need to be its own dynamic array function, but not sure how to get the first row to correspond to the first row of the Item column, the second row to the second, etc., or if it is even possible.

As a general rule, I prefer to do this in Power Query, which is easy, but that requires end users to do a refresh step. If they don't their reports are wrong. Dynamic arrays seems to be an ideal solution as it is automatically calculated.

I'd love to see a GROUPBY() function that was something like:
GROUPBY(DataRange,GroupBy1Column,GroupBy2Column,GroupBy3Column,....,Type1,Column1,Type2,Column2,Type3,Column3,....)

Where Type1 would be something along the lines of the SUBTOTAL() arguments, SUM, AVERAGE, MIN, MAX, etc. and once you started one of those, the function knew you were switching from the grouping columns to the summarization columns.

Silver Contributor

@Ed Hansberry 

Like you, I would start with

= UNIQUE( SampleData[Item] )

Then because I abhor the concept of direct references, I named the formula (anchor) cell 'Item'.

The grouped totals are then obtained by using the array as an argument within the SUMIFS,

= SUMIFS( SampleData[Sales], SampleData[Item], Item# )

The result is dynamic and responds both to editing an item or appending data to the Table.

 

Note: I believe the term for this array behaviour of SUMIFS is 'lifting'

Version history
Last update:
‎Feb 20 2019 07:45 PM
Updated by: