Forum Discussion
Re: ByRow function produces two different results depending on whether row is range or array
In the traditional spreadsheet, it is 'all about the cell'. To process arrays, one treats each term as a scalar and writes an array of formulas, each of which processes a single value. The fact that nested arrays were not supported was not a problem because little use was made of arrays.
Now, in Excel 365, it is the array that is the basic building block of practically all solutions. The limitation of nested array not being supported was retained to maintain backward compatibility. The problem is that the rule is fundamentally incompatible with array working. Any 2D array can be regarded as a column (array) of rows (arrays). TOROW will convert a 2D array to the equivalent 1D nested array and WRAPROWS reverses the process. I believe that to specify Lambda helper functions so that they will not support these nested arrays is a monumental error; they are a fundamental part of spreadsheet calculation.
It is rather like specifying a cup, and then adding 'hot liquids are not supported'. 'About as much use as a chocolate teapot' comes to mind. No matter how skilled the chocolatier is at his craft, the result is not going to be good!
1 Reply
IMHO, that's all about auto-lifting. Every cell in Excel (as well as every range) is actually 2D array, After that auto-lifting works to convert into the single value. Or doesn't work, depends on situation. With ranges is more complex, and with array of arrays doesn't work at all.
ExcelScript gives more clear and straightforward picture having practically no assumptions in converting arrays.
