Forum Discussion
PeterBartholomew1
Feb 02, 2024Silver Contributor
Working with Binary numbers BASE(..., 2, 7) and bit operations BITXOR, BITAND
I recently came across an Excel challenge from Chinmay Amte on LinkedIn and I plan to post my solution here because it contains some 'off the beaten track' techniques that might be of some interest. ...
Patrick2788
Feb 04, 2024Silver Contributor
I've used BITXOR and BITAND very sparingly and I've played with BITLSHIFT and BITRSHIFT. I understand what the functions do but have not used them enough to the point where I can conceptualize a solution for combinations/permutations. I'll study your solution provided to see where I can find ways to make use of these functions.
My current way of tackling this task is with the help of DEC2BIN. I understand the limitations involved but for 127 possibilities it's more than up to the task (for much more than 127 perhaps Python is the best option.)
'GenerateCombinations
=LAMBDA(party,seats,combinations,LET(
header, HSTACK(TOROW(party), "Seats"),
n, COUNT(seats),
seq, SEQUENCE(n),
combinations, SUM(COMBIN(n, seq)),
BIN_vector, DEC2BIN(SEQUENCE(combinations), n),
height, ROWS(BIN_vector),
GetSeats, LAMBDA(r, c,
LET(
bin_val, MID(INDEX(BIN_vector, r), c, 1),
seat_total, INDEX(seats, c),
bin_val * seat_total
)
),
SeatMatrix, MAKEARRAY(height, n, GetSeats),
CoalitionSeats, BYROW(SeatMatrix, SUM),
CoalitionMatrix, FILTER(HSTACK(SeatMatrix, CoalitionSeats), CoalitionSeats >= required),
Results, SORT(CoalitionMatrix, n + 1),
VSTACK(header, Results)
))