Forum Discussion

davidleal's avatar
davidleal
Iron Contributor
Mar 24, 2023

Another issue with BYROW

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 ...
Excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 24, 2023

    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). 

     

JosWoolley's avatar
JosWoolley
Iron Contributor

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

davidleal's avatar
davidleal
Iron Contributor
Mar 24, 2023
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!