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