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
mtarler's avatar
mtarler
Silver Contributor
Nov 18, 2022

I don't have an answer why right now but your 'wrong' answer are just your original values NOT averaged offset by 7. That is probably a good clue what is happening.

One more clue: This variation also works:

=LET(Values, SORTBY(Data[S&P BSE Sensex],Data[DATE]),
     Sqn, SEQUENCE(ROWS(Values)),
     Seven, SEQUENCE(7),
     BYROW(Sqn, LAMBDA(a,
         IF(a<7,"NA", AVERAGE(INDEX(Values, a-7+Seven))))))

Basically setting up the {1,2,3,4,5,6,7} array ahead of time and then simple math inside

  • 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