Forum Discussion
Working with Binary numbers BASE(..., 2, 7) and bit operations BITXOR, BITAND
Confession.
I have changed the code that checks whether a given proposed coalition contains a smaller viable coalition. Originally I worked with arrays of the form {1,1,0,0,1,0,1} to check the subset condition and that required nested MAPs which were not pretty. Since BITXOR and BITAND work with the decimal representation (i.e. scalars rather than arrays) that part could be simplified.
HasSubsetλ
"Tests whether each of an array of sets is a superset of the others"
= LET(
q, TRANSPOSE(p),
s, SIGN(IF(p <> q, BITAND(BITXOR(p, q), q) = 0)),
BYROW(s, LAMBDA(x, SUM(x))) > 0
)
Sadly, settling for the first thing that works has its shortcomings.
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)- 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.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.
Thank you. Amazingly compact, equally opaque!
Pseudo random numbers are something of a closed book to me but could be useful nevertheless.
As you suggest, I can see me using them to generate test datasets without the volatility of RANDARRAY.
