SOLVED

Dynamic Array formula not backward compatible

Copper Contributor

Hello.  I have the Excel Insider edition and have used the new Dynamic Array formulas as follows:

 

=SORT(FILTER(CHOOSE({1,2,3},t_Main[Rank],t_Main[Field 2],t_Main[Total]),t_Main[Rank]<(J3+1)),1)

 

This function works fine for me, and it SPILLs correctly as I would like.  

I have passed my worksheet on to a colleague who does not have Insider Edition (he has the "normal" 365 channel). 

 

My understanding was that you would need Insider Edition to use the new Dynamic Array formulas, but that previous versions of Excel would be backward compatible and would be able to use the resulting spreadsheets without changing the formulas.

 

This seemed to be the case for my colleague, as the formula was converted to something else on his non-Insider Excel:

 

=_xlfn.UNIQUE(_xlfn._xlws.SORT(_xlfn._xlws.FILTER(CHOOSE({1,2,3},t_Main[Rank],t_Main[Field 2],t_Main[Total]),t_Main[Rank]<(J3+1)),1))

 

 

He can see the results of the spilled formula without a problem HOWEVER ... if he changes any of the data that causes the SPILLed results to change, he gets a #NAME error rather than the new formula result.

 

 

So, my question is:

 

Is it right that previous versions of Excel (particularly 365) should be backward compatible for the ARRAY formulas?  Is this the expected behavior, an issue, or a potential setup problem on the other machine?

 

 

Any input appreciated...

 

 

thanks

BrianGGG

 

 

12 Replies

IMHO, there is no backward compatibility from using of these 6 or so functions point of view, you may see only result.

Compatibility works in the other direction.  Anything your colleague does should be usable by you, even if the occasional 'SINGLE' has been inserted, but past versions cannot be expected to emulate functionality they do not possess.

Not sure I catch that. Yes, compatibility in other direction works fine, in modern Excel appears SINGLE but formulas works. Backward compatibility is the question, it doesn't work. At least not always. Even if you don't use new functions of the modern Excel.

Yes, I guess it makes sense that previous versions would not have access to the new functionality.

 

This is just, for lack of a better term, a huge bummer.  What this means is that I (with my Insider Edition) can not pass on any spreadsheets with the new formulas to my colleagues (who have regular 365).

 

Very limiting until 365 gets the new array functions...

 

 

 

BrianGGG

I suspect there is worse to come.  If you as a developer wish to provide solutions built upon Office 365 and your client uses Office 2016/2019 it may be years before you can move beyond the legacy techniques of relative referencing and copy down; we will still have to live with 'fills' when we really want 'spills'.

 

For me, after only a couple of weeks with dynamic arrays the thought of doing without SEQUENCE and FILTER fills me with horror.  FILTER seems to be capable of replacing: LOOKUP; VLOOKUP; HLOOKUP; MATCH/INDEX combo, SUMIF, COUNTIF, AVERAGEIF, SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS.  I am not saying it is always possible or that efficiency considerations will not determine otherwise, but it's a pretty impressive list for starters.

best response confirmed by BrianGGG (Copper Contributor)
Solution

 

Unless I am getting myself confused, it is backward compatibility that is enabled by the SINGLE that inserts implicit intersection into the process, as was always the case for the traditional Excel calculation.  Forward compatibility in which old versions are expected to emulate dynamic arrays will be more of an issue.

 

Mind you, I seem to be the only person on the planet who consistently used named formulae to bypass the Excel's implicit intersection trick and allow me to perform many array calculations without CSE.  They work equally well as traditional or new dynamic arrays.

 

 

If I judge by how long it took for "mainstream" users to get the new IFS or TEXTJOIN functions, it's going to be a very long time before all of 365 has the new Dynamic Array functions.

 

It seems like it would be a great step forward if the dynamic functions were compatible with 365 as an interim step.  365 users could then use spreadsheets with Dynamic functions without error, but couldn't use the new functions themselves.

 

As it is now, I can't use any of the new functions in any spreadsheet that I am sharing with my co-workers ... none of which have Insider Edition.

 

 

 

BrianGGG

@BrianGGG 

 

I suspect that the new functions are the least part of the problem.  Before, each cell address would be linked to a single value, now it has to be capable of storing and returning an variable dimension 2D array.

 

I can understand your frustration.  The formula you shared is pretty sophisticated in terms of Excel programming so clearly you would wish to share your work.

Thanks for responding, much appreciated.

 

Just to be clear on why it's important to share:  this is a question of utility rather than vanity.  

For example, I just created a "tracker" that would be shared by multiple people at my office. 

 

I immediately had to "walk back" a number of the new functions that I used in favor of the older, established methodologies because everyone else got formula errors when they started changing the data.

 

Not the biggest tragedy in the world, but it was a pain to translate back SORT(UNIQUE(FILTER(xxx))).

 

 

 

BrianGGG

Just remembered something else I had written before the system wiped my post (no idea where to find autosave?).  If your coworkers are using a corporate version of Office 365, I believe it is possible for IT to move them only the insider program whilst leaving everyone else on the more stable monthly or semi-annual settings.

 

@BrianGGG wrote:

Not the biggest tragedy in the world, but it was a pain to translate back SORT(UNIQUE(FILTER...

Painful enough, even setting up a sequence counter (yet again) with

= ROW( INDEX(Sheet1!C,1) : INDEX(Sheet!C, n) )

grates after a while.

 

I have the same issue but I think there is a solution to this. I just did a small test by using Office 365 Excel Online version and that worked. The office version I used is from my customer and that even translated the Unique formula into a German one. I tried to also use textjoin and that also got translated as a formula.

Try it.

Kind regards,
Jaap
I have the same issue and just did a test by opening a file like that in the 365 online version and that works fine.
Just explain the people you send this to to open it with the online version.

Kind regards,
Jaap
1 best response

Accepted Solutions
best response confirmed by BrianGGG (Copper Contributor)
Solution

 

Unless I am getting myself confused, it is backward compatibility that is enabled by the SINGLE that inserts implicit intersection into the process, as was always the case for the traditional Excel calculation.  Forward compatibility in which old versions are expected to emulate dynamic arrays will be more of an issue.

 

Mind you, I seem to be the only person on the planet who consistently used named formulae to bypass the Excel's implicit intersection trick and allow me to perform many array calculations without CSE.  They work equally well as traditional or new dynamic arrays.

 

 

View solution in original post