Technically, this is a huge deal for Excel because it represents a fundamental change from the traditional one-value-per-cell model. From a practical usage perspective, though, it may not be as revolutionary as it initially sounds.
I was playing around with it this morning and tested it on a fairly large Excel model with lots of spillable dynamic arrays used as intermediate calculations. I was able to replace those spill ranges with arrays in cells, and all the formulas referencing those results continued to work nicely. In many cases, enabling it was as simple as adding {} around the existing dynamic array formula. That huge workbook immediately looked much cleaner without all those helper spill ranges taking up space.
I think that's where the immediate value is. It simplifies helper ranges and intermediate calculations, reduces potential #SPILL! issues, and makes complex models easier to build, understand, and maintain.
It could also be interesting when combined with LET and LAMBDA, allowing custom formulas to work with arrays without exposing all their intermediate results across the worksheet.
I think it will become considerably more useful, once it supports features like charts, Power Query, and data validation.
I won't be sad to see the end of spilling array formulas -- they were such a hack that often didn't play well with how spreadsheets were conventionally structured in the industry I worked in, as forms.
I do hope that they add more compound types to cells -- sets may be good as well.
ukadakal · · focus · HN ↗
I was playing around with it this morning and tested it on a fairly large Excel model with lots of spillable dynamic arrays used as intermediate calculations. I was able to replace those spill ranges with arrays in cells, and all the formulas referencing those results continued to work nicely. In many cases, enabling it was as simple as adding {} around the existing dynamic array formula. That huge workbook immediately looked much cleaner without all those helper spill ranges taking up space.
I think that's where the immediate value is. It simplifies helper ranges and intermediate calculations, reduces potential #SPILL! issues, and makes complex models easier to build, understand, and maintain.
It could also be interesting when combined with LET and LAMBDA, allowing custom formulas to work with arrays without exposing all their intermediate results across the worksheet.
I think it will become considerably more useful, once it supports features like charts, Power Query, and data validation.
nxobject · · focus · HN ↗
I do hope that they add more compound types to cells -- sets may be good as well.