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.
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
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}, ...) -> NumberIt 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.
As a comment, auto-lifting is an issue in DA Excel, never know for sure when and how it works.
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..
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:
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
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?
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.
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.
- 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)- 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.
