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