Recalculate Split Binary Columns

[tezza]

Firstly, forgive to code tags, I can't seem to work out how to attached a sheet with excel genie.

Looking at the chart below the top row is the binary header value for the columns and each row has been split into single cells.

The first block uses the values 3,4,5,6,7 converted to binary.

The bottom row sums the value of each column - pretty straight forward so far.

The totals of block one shows 0 0 4 3 3 (This row is all that is important in the final outcome)


Is there a way to get excel to adjust only one of the values to make all the columns add up to an even number then show what needs to be changed?

The second block is an example of the goal.

In this case, adjusting the value 7 from row 5 to the value of 4 the total of all the individual columns come to 0 0 4 2 2 making all even numbers.

Only one number can ever be changed at any one time but to know what row and number to adjust and to what is the key to it all.

The adjusted number must always be lower than the original.

16	8	4	2	1		Adjusted       16	8	4	2	1

0	0	0	1	1		3		0	0	0	1	1
0	0	1	0	0		4		0	0	1	0	0
0	0	1	0	1		5		0	0	1	0	
0	0	1	1	0		6		0	0	1	1	0
0	0	1	1	1		4		0	0	1	0	0

0	0	4	3	3	<- total ->        	0	0	4	2	2

Thank you
Terry

 

[Eric W]

Shorter 2007 versions:

L3: =LOOKUP(2,1/((MMULT(ISEVEN(MID(DEC2BIN(D3+1-ROW(INDIRECT("1:"&D3+1)),5),{1,2,3,4,5},1)+$F$8:$J$8-F3:J3)+0,{1;1;1;1;1})=5)+(D3+1-ROW(INDIRECT("1:"&D3+1))=D3)),D3+1-ROW(INDIRECT("1:"&D3+1)))

L4: =IF(SUMPRODUCT(--(D$3:D3=L$3:L3))=ROWS(D$3:D3),LOOKUP(2,1/((MMULT(ISEVEN(MID(DEC2BIN(D4+1-ROW(INDIRECT("1:"&D4+1)),5),{1,2,3,4,5},1)+$F$8:$J$8-F4:J4)+0,{1;1;1;1;1})=5)+(D4+1-ROW(INDIRECT("1:"&D4+1))=D4)),D4+1-ROW(INDIRECT("1:"&D4+1))),D4)

 

[tezza]

Shorter 2007 versions:

L3: =LOOKUP(2,1/((MMULT(ISEVEN(MID(DEC2BIN(D3+1-ROW(INDIRECT("1:"&D3+1)),5),{1,2,3,4,5},1)+$F$8:$J$8-F3:J3)+0,{1;1;1;1;1})=5)+(D3+1-ROW(INDIRECT("1:"&D3+1))=D3)),D3+1-ROW(INDIRECT("1:"&D3+1)))

L4: =IF(SUMPRODUCT(--(D$3:D3=L$3:L3))=ROWS(D$3:D3),LOOKUP(2,1/((MMULT(ISEVEN(MID(DEC2BIN(D4+1-ROW(INDIRECT("1:"&D4+1)),5),{1,2,3,4,5},1)+$F$8:$J$8-F4:J4)+0,{1;1;1;1;1})=5)+(D4+1-ROW(INDIRECT("1:"&D4+1))=D4)),D4+1-ROW(INDIRECT("1:"&D4+1))),D4)

That works like a charm - both VBA and formula now show the same results.

Thank you for all your effort, it's really appreciated.

Now everyone can be Nim pro's :D

 

[Eric W]

Glad to help, that was fun to figure out.

 

[tezza]

Just wanted to share an update, this is spot on with 32 straight wins. I think the game went on loop then giving the same pearls so figured I've gotten as far as I can. The game goes to 7 rows so I had to do a little adaption but all good

Thank you again - now to learn to code

 

[Eric W]

So you're using my formulas to beat up on some poor unsuspecting computer game?!

The macro code was designed to handle pretty much unlimited rows. Just select a larger range. It will handle up to 1024 in each row too. It looks like you figured out how to change the formulas for more rows.

And you'll probably like learning to code. I find it more satisfying to find a good algorithm to beat Nim, than I do to just play Nim. Good luck!