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.
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
That'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
