Forum Discussion

Charley Kyd's avatar
Charley Kyd
Copper Contributor
Apr 04, 2022

SUBTOTAL, OFFSET, and bogus results

In this formula... =OFFSET(Values,0,0,SEQUENCE(10)) ...Values is a column of ten numbers. And the formula returns ten #VALUE! errors, presumably because Excel can't (yet?) manage an array of arrays...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 18, 2022

    JMB17 

    With VSTACK it's bit easier

    =IFNA( DROP(
            REDUCE(0, SEQUENCE( ROWS(values)),
                 LAMBDA(a,v,
                    VSTACK(a,
                       TRANSPOSE(SCAN(, INDEX(values, SEQUENCE(v) ), LAMBDA(a,v,a+v) )   )
                     )
               )
            ), 1 ), "")

    Not sure why simple REDUCE repeats accumulator in first row

    =IFNA(
       REDUCE(0, SEQUENCE( ROWS(values)),
           LAMBDA(a,v,
               IF( v = SEQUENCE(v),
                   TRANSPOSE(SCAN(, INDEX(values, SEQUENCE(v) ), LAMBDA(a,v,a+v) )   ),
                   a
               )
           )
       ), "")

    lori_m , perhaps you may explain

Resources