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

lori_m 

As a comment, auto-lifting is an issue in DA Excel, never know for sure when and how it works.

lori_m's avatar
lori_m
Iron Contributor
May 18, 2022

SergeiBaklan 

Interesting behaviour, I hadn't noticed that before...
For 1x2 array, scalars can get propagated using similar IF logic:

=IF({1;2}=1,1,{2,3})

Compare with VSTACK which has a #N/A in place of the 1 in 2x2 result matrix:

=VSTACK(1,{2,3})

Perhaps this is related to the REDUCE results? I didn't check the formula logic thoroughly.

 

And yes it's true 'auto-lifting' is subject to basically the same restrictions in 365 versions - though a few functions like INDEX work more consistently now. We also now have the MAP function for use in cases where 'auto-lifting' doesn't happen eg SUM and AGGREGATE - or perhaps SCAN, etc. to optimise for speed..

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

    lori_m 

    Yes, IF() with array reserves space for max of parameters and fill it with one which meets condition. But in our case on first step that's like

    =IF( {TRUE}, 11, 11) and it shall be single value returned.

    REDUCE() with first parameter has n+1 steps compare to n steps if parameter is omitted, perhaps here we have two elements array space.

    Will play with it more.

Resources