Apr 04 2022 01:06 PM - edited Apr 04 2022 02:44 PM
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?
Apr 04 2022 06:23 PM
Apr 05 2022 02:14 AM
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.
Apr 05 2022 08:44 AM
Apr 05 2022 09:03 AM
Apr 05 2022 09:49 AM
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.
Apr 06 2022 06:48 AM
Apr 06 2022 12:56 PM
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.
Apr 07 2022 11:43 AM
Apr 08 2022 01:07 PM
AGGRERGATE has reference and array forms and works natively with arrays only for functions starting from #14 and above. For example, both
=AGGREGATE(14,,{1,2,3}, 1)
=AGGREGATE(15,,SEQUENCE(5),2)
work
Apr 08 2022 11:23 PM
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?
Apr 09 2022 06:33 AM
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.
May 17 2022 02:06 PM
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:
May 18 2022 02:48 AM - edited May 18 2022 03:02 AM
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.
May 18 2022 06:07 AM
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
May 18 2022 06:09 AM
As a comment, auto-lifting is an issue in DA Excel, never know for sure when and how it works.
May 18 2022 07:02 AM - edited May 18 2022 07:04 AM
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..
May 18 2022 07:47 AM
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.
May 18 2022 08:37 AM
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.
May 18 2022 01:48 PM
Yes, I've seen that rule here Yellow (lambdadays.org)
However, =N(A1:I1) returns first element; =N(+A1:I1) returns spill.