 # SUBTOTAL, OFFSET, and bogus results

Occasional Contributor

# 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.

However, it DOES retain that two dimensional array, because this formula spills three values:
=INDEX(OFFSET(t.Value,0,0,SEQUENCE(10)),3,1)

And if I wrap SUBTOTAL around the OFFSET formula, I get the results I expect. But I don't get them for AGGREGATE.

How is SUBTOTAL able to provide an array of cumulative results (for SUBTOTAL-9) while AGGREGATE doesn't?

26 Replies

# Re: SUBTOTAL, OFFSET, and bogus results

You need to correct in your offset function. Width that represent how many columns is missing in the formula.
=OFFSET(reference, rows, cols, [height], [width])
The formula must be as below.
=OFFSET(Values,0,0,SEQUENCE(10),1)

# Re: SUBTOTAL, OFFSET, and bogus results

My guess OFFSET with SEQUENCE returns array of references on A1, A1:A2, A1:A3, etc. Itself it's yes, array of arrays, but SUBTOTAL correctly aggregates each of them and return array of results.

AGGREGATE takes entire array of arrays and returns an error.

Don't know what is the logic behind.

# Re: SUBTOTAL, OFFSET, and bogus results

@Starrysky1988
Actually, no. The width is an optional argument. By default, it has the shape of reference, which is Values in this case. So, because Values is a single column, the width parameter isn't needed.

# Re: SUBTOTAL, OFFSET, and bogus results

@Sergei Baklan
The logic behind it is what I was hoping to discover. LOGICALLY, it shouldn't work any differently than SUM, which returns the grand total of all ten arrays—because there are no hidden rows in arrays. And LOGICALLY, it shouldn't work any differently than AGGREGATE. (Both 9 and 109 work with SUBTOTAL.)

I'm delighted that it DOES work. But I sure wish I knew why. Right now, the only thing that makes any sense is that whoever coded the function used an approach that accidentally produced SUBTOTAL's unique results when used with an array of arrays.

# Re: SUBTOTAL, OFFSET, and bogus results

SUM and AGGREGATE takes array of arrays as entire array and return an error. Why SUBTOTAL works with each subarray separately I'm not sure, will try to ask other people.

# Re: SUBTOTAL, OFFSET, and bogus results

@Sergei Baklan
With regard to SUM, there's no reason why it shouldn't work like SUBTOTAL in this instance.

That is, this formula SHOULD be able to work like SUBTOTAL does:
=SUM(OFFSET(Values,0,0,SEQUENCE(10),1))

And then, if we wanted the grand total (which is meaningless in this case), we could use:
=SUM(SUM(OFFSET(Values,0,0,SEQUENCE(10),1)))

# Re: SUBTOTAL, OFFSET, and bogus results

They have different logic inside. SUBTOTAL works with references, OFFSET returns references and combination works.

SUM works with arrays (or ranges auto-lifted to arrays),

For example let say we have =SEQUENCE(5) in A1

=SUBTOTAL(9, A1#) works

=SUBTOTAL(9, SEQUENCE(5) ) doesn't work

SUM works with both.

I only try to say that ranges and arrays are different things. Internal logic in "old" could be different and they differently use auto-lifting. In Pre-DA Excel that was not critical. Similar as we didn't care about implicit intersection. In DA Excel we see different behavior, sometimes unexpected. We may only take it as it is, I don't think Microsoft will re-write developed for 30+ year by different people and teams functions. Easier to develop new Excel from scratch.

As for =SUM( OFFSET( values,0,0,SEQUENCE(10) ) ) - can't say I understand the logic. It calculates number of entities in values multiplied on first element value.

# Re: SUBTOTAL, OFFSET, and bogus results

@Sergei Baklan

That's an interesting insight. But it's hard to generalize from it. For example, AGGREGATE's third argument specifies an array option. But as with SUBTOTAL, these fail:

=AGGREGATE(9,,{1,2,3})
=AGGREGATE(9,,SEQUENCE(5))

(I tried adding a dummy [k] value as the fourth argument, but that was no help.)

Fun stuff!

# Re: SUBTOTAL, OFFSET, and bogus results

AGGRERGATE has reference and array forms and works natively with arrays only for functions starting from #14 and above. For example, both

``````=AGGREGATE(14,,{1,2,3}, 1)
=AGGREGATE(15,,SEQUENCE(5),2)``````

work

# Re: SUBTOTAL, OFFSET, and bogus results

Doesn't =OFFSET(Values,0,0,SEQUENCE(10)) return an array of arrays that are different sizes? In my experience, excel won't return an array where the individual rows or columns are variable sizes to the worksheet. But, wrapping it with subtotal or index returns an array that is consistently sized, so that works.

I'm not yet familiar with the new lambda functions, so don't know if there is a way to try to resize the individual rows/columns so they're all consistent and fill in the empty elements?

# Re: SUBTOTAL, OFFSET, and bogus results

Actually it returns array of references on the ranges. Result depends on what we do with that array. If land into the grid we resolve reference and actually that is, yes, array of arrays of different size.

However, we may take one element from such array and it returns it as an array And, as we see, SUBTOTAL also works with such array. It aggregates each referenced range and returns an array of results.

# Re: SUBTOTAL, OFFSET, and bogus results

I happened across this blog and it reminded me of this post, as you mentioned excel being able to handle an "array of arrays". So, I thought I would leave it here in case you've not yet come across it.

https://www.flexyourdata.com/blog/what-is-a-thunk-in-an-excel-lambda-function/

So, it would appear an array of subtotals can be done: # Re: SUBTOTAL, OFFSET, and bogus results

A late post here but I thought it might be of interest that AGGREGATE actually can process OFFSET with array arguments in 4th, 5th, ... just not 3rd position: Note:

- Arguments taking references can be detected by stepping through evaluate formula.

- Arguments taking arrays show as arrays in the formula arguments dialog.

From this info we can generate a 'function signature' (ref: Yellow (lambdadays.org) )

``````AGGREGATE: (Number,Number, {Range,Array}, Range, Range, ...) -> Number
SUBTOTAL: (Number, Range, Range, Range, ...) -> Number
SUM: ({Number, Range, Array}, {Number, Range, Array}, ...) -> Number``````

It appears that when a formula like OFFSET(A1:A3,,,{1;2;3}) is passed to an argument that accepts only ranges it processes the array of ranges {A1;A1:A2;A1:A3} by 'auto-lifting' giving an array of results.

On the other hand if the same OFFSET formula is passed to an argument that takes arrays, the array argument is processed an array of values and produces a single erroneous result.

My take is that arrays of ranges should be deprecated in 365 as there are alternative ways to achieve the same results with Dynamic Arrays that are non-volatile and work with arrays not just ranges.

# Re: SUBTOTAL, OFFSET, and bogus results

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

# Re: SUBTOTAL, OFFSET, and bogus results

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

# Re: SUBTOTAL, OFFSET, and bogus results

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..

# Re: SUBTOTAL, OFFSET, and bogus results

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.

# Re: SUBTOTAL, OFFSET, and bogus results

Yes, maybe it's the second iteration in the REDUCE that is causing the problem - giving the two element array space? I guess that might be confirmed by gradually increasing the size of the input array 1,2,3...

And I'm certainly not clear on all the details either but the Simon Peyton Jones video clarified some of the key aspects of array processing for me. In particular one of the slides in the presentation states the following rule, the last of these bullet points is referred to as auto-lifting:

Auto-lifting/coercion rule: when computing F(X), and the value of X is
not acceptable to F (based on its signature) then
• If X is a reference, de-reference it, and try again
• If X is a string, parse it to a number and try again e.g. SQRT( “4.5” )
• If X is an array, apply F to each element of the array.

In practice the function tools can give clues about the signature. The suggestion above resulted from trying lots of different types of test values in the function argument dialog (array, number, string, bool), then Evaluate Formula can be used to check if a function takes references.

# Re: SUBTOTAL, OFFSET, and bogus results

Yes, I've seen that rule here Yellow (lambdadays.org)

However, =N(A1:I1) returns first element; =N(+A1:I1) returns spill.