CODEWITHSUNDEEP
excelexcel-formulaexcel-365microsoft365
yendang9423082
yendang9423082

Reputation: 365

How to make lambda function working on excel365?

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?

enter image description here

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

enter image description here

Upvotes: 4

Views: 654

Answers (4)

EEM
EEM

Reputation: 6660

Problem: It seems that the BYROW function is not performing as expected in this formula:

= BYROW( SEQUENCE( 5 ), LAMBDA(Sqn, TEXTJOIN( ",", , SEQUENCE( 5, , Sqn ) ) ) )

As noted in the comments if the array parameter SEQUENCE(5) is replaced by a range (e.g., $B$3# as in the picture below) the formula returns the expected result.
enter image description here

Now if the range $B$3# is the result of the array parameter in the original formula, why the original formula “does not show" the expected result?.
enter image description here

Let’s enter the problem formula in cell [F10], then press F2 to edit, then press F9 to calculate the result of the formula, …
enter image description here

then press enter, and we’ll see the expected result in the range [F10:F14].
enter image description here

Well, it seems that the formula is working as expected, however, it’s “not showing” the correct result. This behavior is due to Excel's upgraded formula language.

Before the implementation of dynamic arrays, the default calculation was “Implicit Intersection” (i.e., reduce many values to a single value), as such, there was the need to wrap the legacy array formulas with braces {} to show multiple values. Now, with the dynamic arrays, the default is to “spill” the results (i.e., excel will dynamically size the output range\array as required by the multiple values generated).

In this case, the output of the LAMBDA function has many values, therefore the result is “spilled”, however, we need to combine them into one single value, and for this purpose, we use the Implicit intersection operator: @.

The formula shall become:

= BYROW( SEQUENCE( 5 ), LAMBDA(Sqn, TEXTJOIN( ",", , SEQUENCE( 5, , @Sqn ) ) ) )

Now let’s enter the formula above in cell [D18].
Excel will propose a variation of the formula that applies "implicit intersection” to the entire formula, i.e.,

= @BYROW( SEQUENCE( 5 ), LAMBDA(Sqn, TEXTJOIN( ",", , SEQUENCE( 5, ,@Sqn ) ) ) )
enter image description here

Reject the proposed formula, because we need a mixed formula that relies on both array calculation and implicit intersection.
Now we have the expected result in range [F18:F22]
enter image description here

Note that the second SEQUENCE, could either be: SEQUENCE( 5, ,@Sqn ) or SEQUENCE( 1, 5,@Sqn ), both return the same result as they will be joined by the TEXTJOIN function.
enter image description here

For additional information please see:
Implicit intersection operator: @
Dynamic array formulas and spilled array behavior

Upvotes: 2

David Leal
David Leal

Reputation: 6769

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

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

excel output

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.

Gsheet output

Upvotes: 3

P.b
P.b

Reputation: 11653

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)

enter image description here

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) enter image description here

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

Upvotes: 0

iDevlop
iDevlop

Reputation: 25272

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.

Upvotes: 1

Related Questions