How to make lambda function working on excel365?-CodePudding

I'm using excel 365,

Can someone please explain why this function is not working as expected? I don't know what is the reason behind?

Below is a screenshot of an excel simulation in Google Sheets, which produces the expected result:

CodePudding user response：

It seems to be a bug in BYROW function, because replacing it with MAP works.

=MAP(SEQUENCE(5), LAMBDA(b, TEXTJOIN(",",, SEQUENCE(5,,b))))

By the way this is not the first time that I have seen an unexpected behavior with BYROW that MAP doesn't have it. It seems to be more robust. When LAMBDA for MAP has a single name both functions are equivalent.

It is curious, Google Spreadsheet that emulates Excel functions, doesn't have this odd behavior. It works, but you need to enter the third input argument (start) of SEQUENCE otherwise it returns #NUM!, because the default value is 0 and not 1.

CodePudding user response：

This works well in GSheet (my Excel is 2019)

=BYROW(SEQUENCE(5),LAMBDA(b,TEXTJOIN(",",,SEQUENCE(1,5,b))))

The seconde sequence is changed from empty to 1.

CodePudding user response：

Not an answer to the actual question (why BYROW behaves unpredictable in given example), but an alternate solution to get the desired result:

BYROW is limited in 1D spills only and therefore if we want a 2D spill result we need to use REDUCE.

If you work with REDUCE you could spill the result you like:

=DROP(REDUCE(0,SEQUENCE(5),LAMBDA(x,y,VSTACK(x,TEXTJOIN(",",,SEQUENCE(5,,y))))),1)

You need to declare a start and an array to "loop" through. x and y in this example. It starts at x, then does it's first calculation using y and the result becomes the next x. Using VSTACK makes that visible, otherwise it would do it's calculations until the final y value is used and shows that only. Stacking them makes each calculation visible and unlike BYROW it can spill 2D. Since you started x at 0 without any calculations yet, the first value needs to drop using DROP. The end result is a 2D spill of what you want.

(Could also be used with horizontal arrays and HSTACK.)

You could also spill down and sideways (without TEXTJOIN): =DROP(REDUCE(0,SEQUENCE(5),LAMBDA(x,y,VSTACK(x,SEQUENCE(1,5,y)))),1)

=MAP(SEQUENCE(5),LAMBDA(x,TEXTJOIN(",",1,SEQUENCE(1,5,x)))) Also bypasses the BYROW unexpected result.