Forum Discussion
Working with Binary numbers BASE(..., 2, 7) and bit operations BITXOR, BITAND
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)
))
"but for 127 possibilities it's more than up to the task
(for much more than 127 perhaps Python is the best option)"
I wouldn't give up on the Excel formula language that easily. BASE is good for numbers up to 2^53! Mind you, I had never noticed that DEC2BIN has a 'places' parameter, so I could have used it.
I first saw it when lori_m used it for a Chandoo minimum length formula challenge. My recollection is that it was a case of multiplying by 37 in base 36 to double up the letter encoding a value.
- lori_mFeb 05, 2024Iron Contributor
Peter - you have a better memory than me, it was only after ten minutes of searching I found this (Code Golf) formula for checking if a string contains a repeated character:
=COUNT(SEARCH(BASE(({0,1,2,3,4,5}+{0;1;2;3;4;5}*6)*37,36,2),A1))>0
I remember avoiding the dreaded CSE entry and unnecessary range dependencies introduced by ROW, etc. were an integral part of such challenges. Things are much more straightforward these days - Excel formulas being a bone fide language and all - though maybe not quite as fun 🙂
( https://dhexcel1.wordpress.com/2017/10/03/boolean-formula-for-repetitive-characters-in-a-string/ )
- PeterBartholomew1Feb 06, 2024Silver ContributorI don't know whether to be more impressed by your original answer or by the fact that you can still find it!
BTW I have corrected a critical typo in my post.