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