Forum Discussion

LBROWN7's avatar
LBROWN7
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
lori_m's avatar
lori_m
Steel Contributor

SergeiBaklan 

I don't think we need full support for array of arrays, just maybe additional parameters to SCAN / BYROW / BYCOL to indicate array support. If set, the function would need to use hstack({a},{b}) in place of {{a},{b}} like in workaround formulas. The way these functions are implemented currently may require a fixed output array size allowing for efficient array processing (e.g. modifying in-place) so there could be a valid reason to add an optional parameter here.

 

By the way, python accumulate function uses the same convention as above for first column - I just needed to add a comma to second comment for LBROWN7 formula to parse. This worked ok on hundred records but hung for me on a thousand. Also I think IFNA(...,"") could be sufficient to deal with NA results - no need for MAP - or equivalently .fillna('') in python.

SergeiBaklan's avatar
SergeiBaklan
MVP
Oct 14, 2023

lori_m 

If function by function - maybe. If Microsoft goes this way most probably new functions like SCAN.EXT will be introduced with usual deployment cycle from Beta to production.

Performance is the key here. So far only PeterBartholomew1 approach gives more or less acceptable results on large arrays. As for IFNA() my impression it reduces performance dramatically. MAP() even more.

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Oct 16, 2023

    lori_m 

    Great, it practically doesn't affect performance compare to initial SCAN2.

    Thank you for the link, make the bookmark to read more carefully.

  • lori_m's avatar
    lori_m
    Steel Contributor
    Oct 16, 2023

    SergeiBaklan 

    I had in mind wrapping the result with IFNA(SCAN2(...),"") rather than inserting extra conditions into the recursive definition which would likely have a considerable impact to performance. Using the following recursive let implementation also gave the same results as the LBROWN7 second formula

    =LAMBDA(initial_value, array, function,
    LET(
        ret, LAMBDA(fn, initial_value, array,
            IF(
                COLUMNS(array) = 1,
                function(initial_value, array),
                LET(
                    acc, fn(fn, initial_value, DROP(array, , -COLUMNS(array) / 2)),
                    HSTACK(
                        acc,
                        fn(fn, TAKE(acc, , -COLUMNS(initial_value)), TAKE(array, , -COLUMNS(array) / 2))
                    )
                )
            )
        ),
        IFNA(ret(ret, initial_value, array), "")
    )
)({1, 2}, {2, 3, 4}, LAMBDA(a, b, VSTACK(a, b)))

    P.S. I found this useful for the python code: Anonymous recursion - Wikipedia

     

     

Resources