MicrosoftEarly on within this discussion/announcement you stated
"Your formulas are generating 'Arrays of Ranges'. 'Arrays of Arrays' and 'Arrays of Ranges' are often truncated by the calc engine.
It's always been like this, it's just easier to hit these days."
May I respectfully suggest that such a position will not be good enough in future.
The improvement offered by dynamic arrays is amazing but it should only be the start of raising Excel to the level whereby it becomes a serious professional solution development platform as well as simply a manifestation of end-use-computing. My objective when developing a solution is not merely to use DA where necessary, it is to create a solution that is dynamic in its entirety. As it stands, dynamic array functionality will work well on a single row or column of variable length, but Excel is based upon a two dimensional grid and the assumption should be that any such calculation may be repeated across (or down) the sheet to give an array of results. Simply copying the formula is not good enough since the number of instances will not respond dynamically.
An example is provided by a workbook I wrote to address a challenge set by Oz_Sunray
The number of parties contributing to a shared present buying exercise could easily change so I wanted the table of contributions made towards the presents to be dynamic in both directions. What I wanted to do is use XLOOKUP to generate the table of contributions from the heading row of people's names. What I had to do instead, was look up the final column and use range composition ":" to build the dynamic 2D range.
I then wanted to apply SUMIFS to each of an array of columns (as a single array formula); again, not possible as it stands.
What I did instead was generate a matrix of filters from the criteria an use MMULT to sum the filtered values for each column, giving a matrix of results. That may be OK for me as a professionally-qualified mathematician but it is not what I would wish to offer the Excel community!
I can see that you may well have enough demands upon your time rolling out the existing functionality (which already comes as a huge relief to me as it takes me past the user-hostile environment of CSE arrays) but I believe some planning for full 2D functionality is needed now, if only to avoid the creation of future 'legacy/compatibility' issues that would rate as self-inflicted damage.
p.s. The link is intended to be to onedrive location but I could consider other means of sharing if required.
