Forum Discussion
ByRow function produces two different results depending on whether row is range or array
a) why does it work with a range reference instead of an array? When it's a range, why is the "array of one" treated differently?
As for what is actually happening here is a sample to help:
=AVERAGE(SEQUENCE(5,1,2+{5})) vs =AVERAGE(SEQUENCE(5,1,2+5))
So =AVERAGE(SEQUENCE(5,1,2+5)) eval to =AVERAGE({7;8;9;10;11}) and then =9
BUT =AVERAGE(SEQUENCE(5,1,2+{5})) eval to =7
obviously the {5} is messing it up even though if you use F9 on the SEQUENCE(5,1,2+{5}) you still get {7;8;9;10;11} of which the AVERAGE() should be 9 but my guess is that internally it is treating it as {7};{8};{9};{10};{11} and then AVERAGE(7) ; AVERAGE(8) ; ... and then returning only the first of those results being AVERAGE(7) = 7, but that is more guess than anything
mtarler wrote:
When it's a range, why is the "array of one" treated differently?
A range pointing to a single cell isn't an array, it's a scalar. Try TYPE(A1:A1) & TYPE(A1:A2) & TYPE({1}) & TYPE({1,2}). I wish single element arrays were implicitly converted to scalars.
mtarler wrote:
As for what is actually happening here is a sample to help:
=AVERAGE(SEQUENCE(5,1,2+{5})) vs =AVERAGE(SEQUENCE(5,1,2+5))
My observed behaviour (not sure if it's documented or a bug), is when a function that expects scalar arguments receives an array, it outputs an array with the same shape applying the function.
I.e., SEQUENCE({3}) is the same as MAP({3}, LAMBDA(x, SEQUENCE(x)))
The problem is, when the function returns an array itself like SEQUENCE, you're creating an array of arrays which can't be displayed in the grid. In the former case it just uses the first element in the inner array. In the later, MAP, case it gives #CALC! "Nested arrays aren't supported".
Related question here too https://answers.microsoft.com/en-us/msoffice/forum/all/textsplit-combined-with-byrow-returns-an/9c881cfc-7b2f-460d-b6b2-9e28733e25ac