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.
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.