Dynamic Array Formulas?

Hi Adrian,


I see Excel 365 now has a new calculation engine built around Dynamic Arrays that "spill" into other cells. This looks to me like the biggest change to Excel's core functionality since the introduction of the xlsx file format. Will the current version of FlexCel read files that include Dynamic Arrays? 

I'd appreciate your perspective.

Thanks,

Steve 

Hi Steve,

We've been following Dynamic arrays for more than a year (yes, they have been in beta that long), and yes, we have plans to implement them. It shouldn't be that complex, it is just that the implementation has been changing many times in the beta so we don't want to spend time re-implementing stuff as it changes.

To be sincere, even if they are mentioned everywhere as the "biggest change ever in Excel" and a "a new recalculation engine", I don't find them that useful, and under the hood they are just nicer array formulas (which we already support, and that not much people uses anyway). The "new recalculation engine" is the same as the old with a couple of changes.

Now, the main challenge I see with dynamic arrays is not the implementation (as said they are just nicer  array formulas) but keeping compatibility with older Excel versions that don't support them. But finding a way to not break old formulas is microsoft's job :)  And that is what also has been generating most changes in the beta. 

About a month ago for example, I got some issue with a user in the "Fast cycle" which had created a file with Dynamic arrays (without even being aware what they were) and the file was failing when opened in "not beta" Excel 365. In this case there were tables involved and the "@" notation that you can use in structured references inside tables, like =@Column1, and the newer Excel 365 was interpreting "@" as a dynamic array and not as a structured reference, while the old version would interpret it as a structured reference. That's the kind of stuff that is really difficult to get right here, how to keep old Excel versions from failing, and not that much the implementation.

Are dynamic arrays already out of beta? Since I am in the fast cycle here (mostly so I can follow dynamic arrays closely), I am not really aware if they landed in the normal cycle yet.

Edit: I mixed up dynamic arrays and array formulas. They could choose better names :)

Adrian Gallero2019-11-26 23:07:13

Hi Adrian,


I don't think I am on any special release cycle and I have them. At this point you can assume they are out in the wild. I came across them when I saw this course on Udemy:

https://www.udemy.com/course/excel-dynamic-arrays/

I can see some uses for what I do as I use lists a lot. It seems this is one of the ways Microsoft is trying to keep Excel competitive with tools like R and Python's Pandas library.

Anyway, thanks for the input. I'm delighted that this is on your radar!

All the best,

Steve

I've heard that this is an answer to google sheets, which has that kind of functionality. But that's just hearsay.


And just a note: Be careful with them. With array formulas, which every Excel user wants to use once they discover them, it is very easy to end up doing lots of extra calculations, because you multiply an array of x elements by another and you get x2 elements, then multiply again and you have x4, and that easily get out of control. Normally spreadsheets using array formulas are quite slow to calculate (in Excel or FlexCel). I can see something similar happening here if you are not careful (old array formulas were almost impossible to enter them by mistake, while dynamic arrays can be entered automatically even if you aren't aware of them).