Another issue with BYROW

[davidleal]

I have found the the following formula, doesn't provide the correct result:

=BYROW(SEQUENCE(2), LAMBDA(i, SUM(INDEX({1,2;3,4},i,))))

If I use implicit intersection operator @ it works, for example:

=BYROW(SEQUENCE(2), LAMBDA(i, SUM(INDEX({1,2;3,4},@i,))))

replacing in both cases BYROW with MAP it works. I found out that if I use CHOOSEROWS, it works with and without @ operator:

=BYROW(SEQUENCE(2), LAMBDA(i, SUM(CHOOSEROWS({1,2;3,4},i))))

Is there any explanation to the above formulas, other than a bug or some specification that hasn't been documented properly?

Note: The samples here, shows the issue using BYROW, but using BYCOL produces similar results.

Thanks

[#2]

@davidleal 

Hi David,

Not sure, but it could be the case that LAMBDAs passed to functions such as BYROW and which do not make use of the implicit intersection operator are first computed for each iteration and stored in an internal array prior to being output.

This would fit in with the known issue of INDEX not being able to internally store an array of rows/columns when passed a zero for either its row_num or column_num parameter. In legacy Excel, for example, even though

=SUM(INDEX({1,2;3,4},1,0))

and

=SUM(INDEX({1,2;3,4},2,0))

would resolve to 3 and 7 as desired, it was never possible to coerce Excel to return both of the entire rows in a single construction. I tried many variations on

=SUM(INDEX({1,2;3,4},N(IF(1,{1;2})),0))

but never managed to achieve it.

Perhaps newer functions such as CHOOSEROWS, by virtue of expecting only a single parameter, as opposed to INDEX expecting two, do not suffer this drawback. That said, your workaround involving inclusion of the implicit intersection operator with INDEX is fascinating - seemingly forcing INDEX into behaving as it should.

Hopefully someone else will offer more insight.

Regards

[#3 · Accepted solution]

@davidleal 

Since you use BYROW() each element of SEQUENCE(2) is passed to lambda as an array, i.e. it will be like

INDEX( {1,2;3,4},{i},)

To return entire row in this case you shall use column numbers as array as well

=INDEX({1,2;3,4},{1},{1,2})

MAP() returns single value of the array element, thus it works.

With BYROW() - yes, we need to transform an array to single value, as "@i" or INDEX(i,1,1). 

[#4]

@Sergei Baklan thanks for your response, without being able to debug what is really happening internally it is difficult to understand what is going on.

For example you said that BYROW passes each element as an array and the INDEX function, when rows/columns are indicated as an array, then the other argument should be provided too because using the default values (, or ,0) don't work. Correct? That is why with @-operator works because you cast the array to a scalar value and then default value works on the other argument. Interesting. I didn't know that, because this behavior, when row_num or col_num are arrays, is not explained in  INDEX documentation.

Another curiosity about this issue with BYROW, if you enter the following commands: F2 and then F9 on the formula of my question it shows the result will be {3;7}, but then the result is not this one. So the evaluation is not the same as the final result.

Thanks again for your response,

[#5]

Thanks @JosWoolley for your feedback, the explanation provided by Sergei seems to be reasonable to understand how INDEX works in the case of rows/columns are arrays, that would explain it, even it is not well documented by Microsoft, it is assumed in the INDEX documentation that both can be only scalar values. That would explain BYROW wrong result on my formula. Thanks again!

[#6]

@davidleal 

Yes, BYROW() always passes to lambda entire row, i.e. array of elements. Even if that's only one value it is not auto converted into single value.

Yes, how INDEX() works with arrays of rows/columns is not documented directly. Some explanation is in Can Excel's INDEX function return array? - Stack Overflow and in related links, includes trick with N(IF(..)) which @JosWoolley mentioned. But that all is for pred-DA Excel, behaviour in modern Excel is bit different. N() in it also returns an array.

I'm not sure how exactly INDEX() works with array/columns elements, at least not to explain in details. And the most interesting question is why F9 evaluates result correctly. Perhaps @JoeMcDaid or someone else from the team could give more details.