Forum Discussion
SUBTOTAL, OFFSET, and bogus results
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.
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.
- SergeiBaklan
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)))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.
- SergeiBaklan
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!