Forum Discussion
Recursive LAMBDA implementation of Excel's REDUCE function.
Not a question -- just sharing some interesting code.
Out of curiosity, I decided to develop an implementation of Excel's REDUCE function as a recursive Excel LAMDA function. I was truly amazed how simple it was to write such a powerful function.
=LAMBDA(initial_value,array,CLAMBDA,
LET(
_00,"Implementation of REDUCE in Excel Lambda",
vec, TOROW(array),
rec_L, LAMBDA(acc,cindex,rec_LL,
LET(
cvalue, INDEX( vec,0,cindex ),
new_acc, CLAMBDA( acc, cvalue ),
IF( cindex = COLUMNS(vec),
new_acc,
rec_LL(new_acc,cindex+1,rec_LL )
)
)
),
rec_L( initial_value,1,rec_L)
)
)(0, {1,2,3}, LAMBDA(acc,cv, acc+cv))
Understanding the recursive implementation REDUCE, will allow one to create custom REDUCE functions should your need one.
Note -- the recursive lambda function "rec_L" above does not need to be externally named.
The recursive reference to rec_L occurs at "run time" by including the "rec_l" reference as a parameter to itself : rec_L(initial_value, 1,rec_L) (line 15)
Interesting method using LET recursively like that - thanks for posting.
One way to work around stack limits could be to use a binary tree method:REDUCE2 = LAMBDA(initial_value, array, function, IF( COLUMNS(array) = 1, function(initial_value, array), REDUCE2( REDUCE2( initial_value, TAKE(array, , COLUMNS(array) / 2), function ), DROP(array, , COLUMNS(array) / 2), function ) ) )For a 2D version of BYROW (faster than REDUCE/VSTACK alternative):
BYROW2 = LAMBDA(array, function, IF( ROWS(array) = 1, function(array), VSTACK( BYROW2(TAKE(array, ROWS(array) / 2), function), BYROW2(DROP(array, ROWS(array) / 2), function) ) ) )
These are both based on examples shared by PeterBartholomew1OwenPrice posted a discussion as an article
(2) Excel LAMBDA Spotlight: Bisected Map with BMAPλ | LinkedIn
The focus there is to stack mapped arrays but with minor changes one can apply a closely-related function to a SCAN with array outputs. All that is needed is to pass a term from the prior stack to initialize the following one.
Now as we can get around the unfortunate 'nested array' and 'array of array' issues with recursion, perhaps it is time to go back to Microsoft with the request that they implement such functionality as native formulas. As I travel in the direction of building ever larger elements of any solution within a single Lambda function, hitting these limitations is becoming the norm rather than the exception.
I was vaguely aware of a recommendation for a lambda spotlight article 🙂 A modification for SCAN allowing for column vectors might be,
SCAN2=LAMBDA(initial_value, array, function, IF(COLUMNS(array) = 1, function(initial_value, array), LET( acc, SCAN2(initial_value, DROP(array, , -COLUMNS(array)/2), function), HSTACK(acc, SCAN2(TAKE(acc, , -1), TAKE(array, , -COLUMNS(array)/2), function) ) ) ) )Then the formula in the other LBROWN7 thread returns expected results (with NAs),
=SCAN2({1},{2,3,4},LAMBDA(a,b,VSTACK(a,b)))
I'd think requesting array versions of map and scan functions would be sufficient for a majority of use cases. Going with current trends XMAP and XSCAN might be sufficient if able to accommodate 2D input arrays in row major order.
An advantage of the anonymous recursion implementation is that unchanged variables may be moved outside the recursive definition as in the 'vec' definition in the original post. Additionally, perhaps we can remove the 'func' definition and just use function(array) in the first SergeiBaklan ByRow2 definition.
- Interesting. Of course using recursion may cause you to hit the recursion limit. Perhaps sooner than hitting limits with REDUCE?
What's that CLAMBDA in your formula?
Re: your question what is CLAMBDA?recursive LAMBDA PARAMETERS are : [initial_value, array, CLAMBDA ]
recursive LAMBDA ARGUMENTS are: (0, {1,2,3}, LAMBDA(acc,cv, acc+cv) ]Mapping PARAMETERS to ARGUMENTS, we get:
initial_value => 0
array => {1,2,3}
CLAMBDA => LAMBDA(acc,cv, acc+cv)CLAMBDA is a LAMBDA function that tells REDUCE how to combine the previous accumulator with the current_value, to produce the new accumulator. In my example I am just summing [accumulator + current_value] which results in the sum of the inputted array.
Re: Recursion LIMITSYou are correct -- the maximum vector size I was able to use in the recursive function was about 2,700
while in the real Excel REDUCE I could easily use a vector size of 1 million
=REDUCE(0, SEQUENCE(1,1000000,1,1), LAMBDA(acc,cv, acc+cv))
Lastly:
this code demonstrates how one COULD write Excel's REDUCE in as a Recursive Excel LAMBDA function. It in no way implies one should write it this way. However, if you need to do looping where the number of repetitions is unknown, you will likely need to use recursion.
Hope your questions were answered.Just in case, current recursion stake limit is 16384. It is calculated as number of iterations x number of operands. Still not sure how operands are counted. It looks like you have 6 of them.
As for the REDUCE have no idea about the limit. In your sample that's more limit of SEQUENCE by grid size (1 048 576). If less we could have #SPILL! in case of columns, but if more we have #VALUE! in any case.
As for the idea itself it's great to demonstrate lambda self-calling. That was really actual when we have no helper functions and AFE. With them I'm not sure what are the pros of using LAMBDA/ME technique.
- Ongelmia sisäänkirjaamisessa. Yritä myöhemmin uudelleen. Details
