Forum Discussion

Viz's avatar
Viz
Brass Contributor
Nov 18, 2022

ByRow function produces two different results depending on whether row is range or array

In a nutshell, I am trying to compute average with a combination of ByRow, Index, and Average. I needed a sequence number array for this algorithm. When I put this sequence number in a range and use ...
excel
Viz's avatar
Viz
Brass Contributor
Nov 18, 2022
That is a good catch. So, I think in the bug case, the index is not returning the entire array but it is just returning the first value.
mtarler's avatar
mtarler
Silver Contributor
Nov 18, 2022

Viz  a couple more interesting nuances.  Sending an arbitrary array reference in and then converting that to a number array using ROW() works:

=LET(Values, SORTBY(Data[S&P BSE Sensex],Data[DATE]),
     sqn,A1:INDEX(A:A,ROWS(Values)),
     BYROW(sqn, LAMBDA(in,
           LET(a,ROW(in),
               IF(a<7,"NA", AVERAGE(INDEX(Values, SEQUENCE(7, 1, a-7+1))))))))

but converting that cell reference outside the LAMBDA doesn't work:

=LET(Values, SORTBY(Data[S&P BSE Sensex],Data[DATE]),
     sqn,A1:INDEX(A:A,ROWS(Values)),
     BYROW(ROW(sqn), LAMBDA(a,
           IF(a<7,"NA", AVERAGE(INDEX(Values, SEQUENCE(7, 1, a-7+1)))))))

 

Resources