Forum Discussion
Dynamic Array formula not backward compatible
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.
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.
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
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
