Forum Discussion
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...
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.
- SergeiBaklan
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)))
