Forum Discussion
SUBTOTAL, OFFSET, and bogus results
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.
Yes, I've seen that rule here Yellow (lambdadays.org)
However, =N(A1:I1) returns first element; =N(+A1:I1) returns spill.
I believe the same rule applies in this case too. Stepping through Evaluate Formula:
1. =+N(A1:B1) -> +1
2. =+N(+A1:B1) -> +N(+{1,2}) -> +{1,2}Based on trial results, signature appears to be:
N: ({range, number}) -> number
+ (unary): ({number, text, bool}) -> {number, text, bool}So applying coercion/auto-lifting rule:
1. The range parameter is amenable to the N function and the first element is returned.
2. The range parameter is not amenable to + and the result is dereferenced to an array (or value in older versions that use implicit intersection). The array is then passed to N, via auto-lifting.
One can try substituting N with ABS, which doesn't accept ranges so dereferencing is automatic, or IMABS, which accepts references of single cells only so + is also needed to auto-lift to array.
That's just my understanding which may not be 100% reliable. It would be nice to have documentation of function type info made available as part of the documentation.
BTW, XLM functions use this same function specification - ARGUMENT and RESULT parameters are summed over types: number(1), text(2), bool(4), reference(8), error(16), array(64), eg 95 =any type.
For anyone interested, I just noticed the new TAKE function can return arrays of references just like OFFSET:
=SUBTOTAL(9,TAKE(A1:A10,SEQUENCE(10)))So I guess I might take back what I said earlier as this could be a reasonably efficient approach for running sums and isn't volatile.
PS. Also to clarify above description, the N function seems to be designed to take references and return the first value from the first area in the range. Note Evaluate Formula shows fixed references when clicking Evaluate button: N((A1,A3)) -> N(($A$1,$A$3)) whereas other operations like ABS and + force the range to be converted to values according to the auto-lift rule or else return error.
That's interesting, great finding. DROP also returns references, not sure what could be the practical case.
N() - I guess same behavior, I mean references, for some other functions. For example
EDATE(TODAY(), (A1, A3) ) => EDATE(TODAY(), ($A$1, $A$3) ) on evaluation. 30 years old design, good enough before dynamic arrays come.
By the way, with help of other people found how to remove duplicate of the value for REDUCE in first line. The problem is in seconds step due to IF( {...}, expanding. Without Beta functions correct result is returned by
=IFERROR( REDUCE(0, SEQUENCE( ROWS(values)), LAMBDA(a,v, IF( v = SEQUENCE(v), TRANSPOSE(SCAN(, INDEX(values, SEQUENCE(v) ), LAMBDA(a,v,a+v) ) ), INDEX( a, SEQUENCE(v), SEQUENCE(,v) ) ) ) ), "")other words to take exact accumulator size for the step a => INDEX( a, SEQUENCE(v), SEQUENCE(,v) )