Forum Discussion

Brass Contributor

Oct 09, 2023

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

Excel

Formulas and Functions

SergeiBaklan

MVP

LBROWN7

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.

LBROWN7

Brass Contributor

Oct 10, 2023

Hi Sergei:

Thanks for the info -- I was totally unaware that there was a way to calculate recursion limits ( aside from empirical testing)

Also, took me a while to figure out what you meant by LAMDA/ME. Going forward I will definately use ME for the recursive function name in the parameter list ( very descriptive)

As to your last comment regarding the utility of LAMDA/ME versus using helper functions. At this point the primary utility of LAMBDA/ME technique in this context is educational,

Thanks for your post.

SergeiBaklan

MVP

Oct 12, 2023

LBROWN7

Sorry, LAMBDA/ME is just the slang used at the beginning of lambdas in Excel. That's close to what you suggested. If take the latest of suggested by lori_m functions and put it in the cell without given a name, that could be like

=LAMBDA(array,function,
    LET(
        byRow2, LAMBDA(ME,arr,func,
        IF( ROWS(arr) = 1, func(arr),
            VSTACK(
                ME( ME, TAKE(arr, ROWS(arr) / 2), func  ),
                ME( ME, DROP(arr, ROWS(arr) / 2), func ) )
        ) ),
        byRow2(byRow2, array, function) )
)(range, fn)

or if not to wrap with lambda which, IMHO, more logical for in-cell formula

=LET(
 byRow2, LAMBDA(ME,arr,func,
   IF( ROWS(arr) = 1,
       func(arr),
       VSTACK(
         ME( ME, TAKE(arr, ROWS(arr) / 2), func  ),
         ME( ME, DROP(arr, ROWS(arr) / 2), func ) )
   )
 ),
byRow2(byRow2, array, fn) )