4

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

P.b
  • 8,293
  • 2
  • 10
  • 25
Yen Dang
  • 268
  • 2
  • 9
  • 3
    Interesting. First of all, I suspect that you meant `=BYROW(SEQUENCE(5),LAMBDA(b,TEXTJOIN(",",,SEQUENCE(,5,b))))`, i.e. the 5 within the second `SEQUENCE` function should apply to the *columns* parameter, not the *rows* parameter. Secondly, replacing `SEQUENCE(5)` with a *worksheet range* containing the numbers 1 to 5, e.g. `=BYROW(I2:I6,LAMBDA(b,TEXTJOIN(",",,SEQUENCE(,5,b))))` produces the desired result. I am not sure of the reason for this anomalous behaviour. – Jos Woolley Jan 07 '23 at 09:30
  • 1
    The hypothesis that `BYROW` works with *ranges* but not *arrays* is tempting, though an example such as `=BYROW(SEQUENCE(5),LAMBDA(b,b+1))`, which correctly returns `{2;3;4;5;6}`, refutes it. – Jos Woolley Jan 07 '23 at 11:55
  • 1
    Ok, so it doesn't do what _you_ expect. But what is that? Without telling us what you do expect, any answers here are just speculation – chris neilsen Jan 07 '23 at 22:42
  • 1
    @MayukhBhattacharya I think you misunderstood. I proposed the idea that `BYROW` might work with *ranges*, but not *arrays*, as a potential explanation to the OP's question, but then disproved it myself. – Jos Woolley Jan 08 '23 at 06:33
  • @chrisneilsen a screenshot of the expected behaviour is added. – P.b Jan 08 '23 at 17:59
  • 1
    @P.b not trying to be rude, but how do _you_ know that's what the _OP_ expects? – chris neilsen Jan 08 '23 at 22:57
  • Because that's how the formulas used supposedly behave. – P.b Jan 09 '23 at 06:38

4 Answers4

3

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

David Leal
  • 6,373
  • 4
  • 29
  • 56
  • 1
    `MAP()` works but I dont know why `BYROW()` is not working. Weird i think – Mayukh Bhattacharya Jan 08 '23 at 15:56
  • 2
    For me the only explanation is a bug. For this case the signature of of both functions are the same and the expected output should be the same. – David Leal Jan 08 '23 at 15:58
  • 2
    Good even I found `MAP()` can tackle many situations where other functions can't. – Mayukh Bhattacharya Jan 08 '23 at 16:07
  • 1
    Thanks for pointing that out @MayukhBhattacharya `=MAP(SEQUENCE(5),LAMBDA(x,TEXTJOIN(",",1,SEQUENCE(1,5,x))))` behaves as byrow in this case, but without the bug. Funny that the Google Sheets simulation does not show the bug with BYROW. – P.b Jan 08 '23 at 16:25
  • 2
    `B2#` is a range, but as Jos showed in his post to me is that it does accept an array as an argument, it just does not handle it as we would expect it to in this particular case. – P.b Jan 08 '23 at 17:34
2

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

EEM
  • 6,601
  • 2
  • 18
  • 33
  • Interesting. `BYROW` works using implicit interception (`@`) and it is not required for the `MAP` equivalent approach, even with `MAP` if you use `@` it works too. The `@Sqn` is forced to get just one value instead of an array, but `Sqn` is already a single value (may be it is an array with a single value). Evaluate Formula tool doesn't help because it doesn't provide the steps in `BYROW`. – David Leal Jan 10 '23 at 19:28
  • `Sqn` is an array with either 5 rows or 5 columns. – EEM Jan 10 '23 at 19:38
  • `Sqn` = `SEQUENCE(5)`, So you are saying that SEQUENCE(5) returns just one element, as a matter of fact, it returns 5 elements, of which `SEQUENCE( 5, ,@Sqn )` one as starting point 5 times. – EEM Jan 10 '23 at 19:52
  • That I don't know because `Sqn` represents a single element of `BYROW` input (`SEQUENCE(5)`) on each iteration, right? I tested with this and it returns `1x1` array: `=BYROW(SEQUENCE(5), LAMBDA(b, TEXTJOIN(",",, ROWS(b), COLUMNS(b))))`. It seems to be an array but `1x1` not `5x1` or `5x1`. This may justify using `@`-operator, even for `MAP` is not required. It seems to me it is a bug. The link you shared suggests `@`-opeator can be used for backward compatibility, even if you can use it in other scenarios. – David Leal Jan 10 '23 at 19:54
  • The correct test would be: `=TEXTJOIN(",",, ROWS(SEQUENCE(5)), COLUMNS(SEQUENCE(5)))` which results in `5,1`. – EEM Jan 10 '23 at 19:56
  • yes, but this is a different test, we are trying to test the shape of `Sqn` and for that, you need to test it inside `BYROW`, right? – David Leal Jan 10 '23 at 19:57
  • Not really, using `Sqn` inside `BYROW` returns the same result that you are trying to prove, why it should have a different behavior? If you are really **trying to test the shape of `Sqn`** then test the outcome of the `Sqn` formula on its own, then it can be inferred that it behaves differently in the `LAMBDA` formula within the `BYROW` function, and that is because it’s *spilling* the results, instead of taking the results as a single element, and the proof of this is in the figures 3 & 4, they show that the result of the `Sqn` are *spilled*, that why the use of `@` is required. – EEM Jan 10 '23 at 20:28
  • It’s not a bug as the documentation warns about this situation. The results are the same, only that instead of taking them as a single element they are *spilled*, and this is what the documentation explains, and that’s why the use of `@` is suggested. The `MAP` function does not need the use of the *Implicit intersection operator* probably because is a different function. Suggest to apply the steps used in figures 3 & 4 to this formula: `= BYROW( SEQUENCE( 5 ), LAMBDA(Sqn, SUM( SEQUENCE( 5, ,Sqn ) ) ) )` . – EEM Jan 10 '23 at 20:29
  • Thanks, maybe I don't follow you. The screenshot shows the final result in array format, not each iteration's specific `Sqn` value. Where in the `BYROW` documentation is this behavior explained? It states: "Applies a LAMBDA to each row and returns an array of the results" so each row for `SEQUENCE(5)` will be a `1x1` array, but maybe I am missing something here. – David Leal Jan 10 '23 at 20:50
  • For additional information please see: [Implicit intersection operator: @](https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34) [Dynamic array formulas and spilled array behavior](https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531) – EEM Jan 10 '23 at 20:53
  • back to this @EEM, for the sake of trying to understand how Excel works, I found the following example: `=BYROW(SEQUENCE(2), LAMBDA(i, SUM(INDEX({1,2;3,4},i,))))` it requires `@i` to get the correct result, but on contrary if we use `CHOOSEROWS`, it works with and without `@`: `=BYROW(SEQUENCE(2), LAMBDA(i, SUM(CHOOSEROWS({1,2;3,4},i))))` Why is needed in the first case and in the second it doesn't matter? The logic for using `@`should be the same I would say. – David Leal Mar 24 '23 at 13:28
1

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.

P.b
  • 8,293
  • 2
  • 10
  • 25
  • See my comment to the original question. If `BYROW` is, as you say, "*limited in 2D references (it only uses the first value from the sequence)*", why then does the OP's solution work if the part `SEQUENCE(5)` is replaced with a reference to a range of worksheet cells containing the numbers 1 to 5? – Jos Woolley Jan 07 '23 at 10:25
  • I noticed. I can't explain it, it must've to do with array vs range. I found that BYROW often has it's limitations and therefore tend to look at REDUCE instead. I guess it has something to do with array/range. It's an interesting question though. – P.b Jan 07 '23 at 11:11
  • I thought that, but I tested simplified alternatives with `BYROW`, and it accepts arrays just fine. – Jos Woolley Jan 07 '23 at 11:50
  • 1
    For example, `=BYROW(SEQUENCE(5),LAMBDA(b,b+1))` returns `{2;3;4;5;6}`, as expected. – Jos Woolley Jan 07 '23 at 11:53
  • Strange indeed and unpredictable – P.b Jan 07 '23 at 12:43
1

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.

iDevlop
  • 24,841
  • 11
  • 90
  • 149