Forum Discussion
ByRow function produces two different results depending on whether row is range or array
BYROW() assumes you transfer an array as parameter, even if that array has only one element. SEQUENCE() from array works differently, you may compare =SEQUENCE(2) and =SEQUENCE({2}).
To fix, you may pass first element of each row array
=LET(
Values, SORTBY(Data[S&P BSE Sensex], Data[DATE]),
Sqn, SEQUENCE(ROWS(Values)),
BYROW(
Sqn,
LAMBDA(a,
IF(
@a < 7,
"NA",
AVERAGE(
INDEX(Values, SEQUENCE(7, 1, @a - 7 + 1))
)
)
)
)
)or simply change BYROW on MAP
=LET(
Values, SORTBY(Data[S&P BSE Sensex], Data[DATE]),
Sqn, SEQUENCE(ROWS(Values)),
MAP(
Sqn,
LAMBDA(a,
IF(
a < 7,
"NA",
AVERAGE(
CHOOSEROWS(
Values,
SEQUENCE(7, 1, +a - 7 + 1)
)
)
)
)
)
)which passes to lambda value, not array.
Thanks for the detailed explanation and the solution, Sergei.
I am still trying to process your explanation fully. But after reading your explanation to mtarler and his work around with row function, I think I am getting some sense of the problem.
I am wondering if ByRow is treating the sequence as matrix instead of a vector when we directly pass it in the function (and therefore the array or array problem).
Am I going in the right direction?
Viz , not sure I understood correctly. IMHO, BYROW() never passes the matrix since that's by row. From the grid it takes an array (vector) since the row in the grid is always an array. From the array it takes current value of the array, it could be value or vector. That's our job to transform such vector/value into single value with lambda, otherwise we have array of arrays.
- as always thank Sergei, I seem to always learn something new from you. That said, can you explain:
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 anythingThat's better to ask JoeMcDaid for more professional explanation.
My guess if we have BYROW on spill, aka array, it takes each element of such array which is value. Each element of the range is an array, auto-lifting doesn't work in such case.
As for the SEQUENCE not sure how it works. With an array in step it looks like it returns only the value for the first step.
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
