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
lori_m's avatar
lori_m
Iron Contributor
May 25, 2022

For anyone interested, I just noticed the new TAKE function can return arrays of references just like OFFSET:

 

=SUBTOTAL(9,TAKE(A1:A10,SEQUENCE(10)))

 

So I guess I might take back what I said earlier as this could be a reasonably efficient approach for running sums and isn't volatile.

 

PS. Also to clarify above description, the N function seems to be designed to take references and return the first value from the first area in the range. Note Evaluate Formula shows fixed references when clicking Evaluate button: N((A1,A3)) -> N(($A$1,$A$3)) whereas other operations like ABS and + force the range to be converted to values according to the auto-lift rule or else return error.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 25, 2022

lori_m 

That's interesting, great finding. DROP also returns references, not sure what could be the practical case.

N() - I guess same behavior, I mean references, for some other functions. For example

EDATE(TODAY(), (A1, A3) ) => EDATE(TODAY(), ($A$1, $A$3) ) on evaluation. 30 years old design, good enough before dynamic arrays come.

 

By the way, with help of other people found how to remove duplicate of the value for REDUCE in first line. The problem is in seconds step due to IF( {...}, expanding. Without Beta functions correct result is returned by

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

other words to take exact accumulator size for the step  a => INDEX( a, SEQUENCE(v), SEQUENCE(,v) )

 

  • lori_m's avatar
    lori_m
    Iron Contributor
    May 27, 2022

    SergeiBaklan 

    Nice technique using EXPAND. Maybe more mundanely,

    =IF(SEQUENCE(ROWS(values))>=SEQUENCE(,ROWS(values)),
SUBTOTAL( 9, TAKE( values, SEQUENCE(,ROWS(values)))),"")

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 26, 2022

    lori_m 

    So, with your TAKE() finding our sample could be simplified to

    =REDUCE(0, SEQUENCE( ROWS(values) ),
     LAMBDA(a,v,
       IF( v = SEQUENCE(v),
         SUBTOTAL( 9, TAKE( values, SEQUENCE(,v) ) ),
         EXPAND( a, v, v, "")    )
 )  )

    The only these are Beta functions and works with ranges only.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 26, 2022

    lori_m 

    That's great summary of functions, thank you for sharing. Need some time to play with it.

  • lori_m's avatar
    lori_m
    Iron Contributor
    May 26, 2022

    SergeiBaklan 

    That's a better approach for non-beta version – I am bookmarking for future reference…

     

    RE: N() - I should have included ‘text’ and ‘bool’ as inputs in the function signature before. Input values like “a” and TRUE can be input into the function arguments dialog to verify. I also just noticed that the input type shows ‘any’ initially and follows same conventions as ARGUMENT function displaying the lowest number type or ‘any’ by default to indicate number, text or bool.

     

    For reference, I'm attaching a comprehensive function list which was built a few years ago using XLM and includes formatting for array, reference, or value (number, text, bool or unformatted for scalar only). Functions can be grouped according to rules such as

    - In pre-DA versions underlined inputs/outputs generally need CSE entry (eg IF, TRANSPOSE) while regular inputs with bold outputs need array coercion (e.g. INDEX, VLOOKUP).

    - In all versions, functions with reference inputs/outputs can process arrays of references (e.g. OFFSET, TAKE) but those that also allow value inputs require + to auto-lift ranges to arrays (e.g. EDATE, N).

     

    The list currently only includes Pre-DA functions – I’ll look to update at some point soon…

Resources