Forum Discussion
ByRow function produces two different results depending on whether row is range or array
b) if you hover over the error you are probably getting the array of arrays error
c) a common work around here is to use REDUCE() and 'build' the array inside and a drop to get rid of the initial value. e.g. (note this is untested and just off the cuff as an example)
=DROP( REDUCE(0, A1#, LAMBDA( p, q, HSTACK( p, SEQUENCE( q, 1, q, 0) ) ) ), 1)
Hi Matt
Going straight for VSTACK would avoid the fill of #N/As.
= DROP(
REDUCE(0, seq#,
LAMBDA(p, q, VSTACK(p, SEQUENCE(q, 1, q, 0)))
),
1 )In a properly specified system
= MAP(seq#, LAMBDA(q, SEQUENCE(q, 1, q, 0)))would work. The 'Nested arrays are not supported' #CALC! error is just plain irritating since the overwhelming majority of solutions I work of have arrays of arrays as their solution. Mind you my requirements have grown since I first encountered the 'array of array failure'. I no longer would want to settle for a row of column vectors, packed into a 2D array. Now I want arrays of 2D arrays, just as one could return with H/VSTACK!
- Thanks! I actually used the VSTACK on my own intuition!
QQ - when you say "properly specified system" - how do you mean? Thanks!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!
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.
