Forum Discussion

lori_m's avatar
lori_m
Iron Contributor
Feb 04, 2024

PeterBartholomew1 

Looks like a nice approach, I'd like to make time to check this out in more detail. It does makes me think a lambda function for generating combinations, like the one in Python, would make for a useful utility function and worth looking at as a future challenge.

 

On the subject of more obscure functions, I have seen specialised financial functions such as DOLLARFR and even ODDFYIELD employed in the context of treasury yield computations. Bitwise functions are also fairly niche in my experience but become more useful in lambda development where one is looking to optimise algorithms for example replacing array comparisons with bitwise operations. One application I have found myself reusing on multiple occasions is in generating (pseudo-)random data but removing volatility e.g. for benchmarking or speeding up recalc

 

=LAMBDA(length, [seed],
    TAKE(
        SCAN(
            IF(ISOMITTED(seed), 123456789, seed),
            SEQUENCE(length, , , 0) * {13, -17, 5},
            LAMBDA(s, i, BITXOR(s, BITAND(BITLSHIFT(s, i), 2 ^ 32 - 1)))
        ),
        ,
        -1
    )
)(5)

 

ref: https://en.wikipedia.org/wiki/Xorshift

m_tarler's avatar
m_tarler
Bronze Contributor
Feb 27, 2024
ooooh pseudo-random number. I totally thing Excel should add [seed] to RAND() and all of its 'sister' functions. That said I created my own pseudo-rand number generator (very primitive but fine for most non-scientific work) and will have to compare it to the option you give here.
I have seen many good options for pseudo-rnd numbers here on the forum when people ask to create 'random' lists for work schedules or baseball call sheets or tournement lists, etc... Having a SEED value based on the date, or week, or whatever will give them that random feel but not change every time they re-open the sheet. thx for sharing.
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Mar 03, 2024

    m_tarler 

    Maybe we need an archive folder to store the definitive recommendations and methods thought to be of long-term value?  I think a pseudo-random number generator would qualify.

    • lori_m's avatar
      lori_m
      Iron Contributor
      Mar 12, 2024

      m_tarlerYes, having a repeatable random function can come in handy, I've quite often divided by 2^32 to get a non-volatile RAND alternative

       

      PeterBartholomew1 Indeed, another possible candidate might be a 'combinations' function,

       

      =combinations({"a";"b";"c";"d"},2)

       

      which would return {"a","b";"a","c";"b","c";"a","d";"b","d";"c","d"}. I had been thinking about this off and on for a while but like SergeiBaklan couldn't see any reasonable route. I revisited again in light of the bisection method you provided recently based on a recursion over an array version of,

       

       

      COMBIN(n,k) = COMBIN(n-1,k-1) + COMBIN(n-1,k)

       

       

      For example with the following lambda definition =combinations(sequence(20),10) returns COMBIN(20,10)*10=1847560 elements.

       

      combinations
=LAMBDA(arr, k,
    IF(
        k <= ROWS(arr),
        IF(
            k = 1,
            arr,
            DROP(
                VSTACK(
                    combinations(DROP(arr, -1), k),
                    EXPAND(combinations(DROP(arr, -1), k - 1), , k, @TAKE(arr, -1))
                ),
                k = ROWS(arr)
            )
        )
    )
)

       

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Mar 16, 2024

        lori_m 

        That was quite an achievement!  I am not sure that my mind is correctly wired for recursion.  I did get there but it was far from easy.

        I picked out one step in your recursive calculation and, at least, it explained why the manner in which the combinations are ordered is a little unusual.

         

        The other thing I noticed was that various nested calculations are performed more than once.  I suppose that goes for the recursive formula for the count of combinations as well

        COMBIN(n,k) 
= COMBIN(n-1,k-1) + COMBIN(n-1,k)
= COMBIN(n-2,k-2) + COMBIN(n-2,k-1) 
                  + COMBIN(n-2,k-1) + COMBIN(n-2,k)

        Something else that had me puzzled for a moment was the type conversion

        @TAKE(arr, -1)

        used to pad the expanded array.

         

Resources