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

 

[tezza]

I think I've worked out Jeanie

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	to BIN	to numbers		Adjust this column		16	8	4	2	1		Adjusted column		16	8	4	2	1
3	11	00011		3		0	0	0	1	1		3	EXAMPLE	0	0	0	1	1
4	100	00100		4		0	0	1	0	0		4		0	0	1	0	0
5	101	00101		5		0	0	1	0	1		5		0	0	1	0	1
6	110	00110		6		0	0	1	1	0		6		0	0	1	1	0
7	111	00111		7		0	0	1	1	1		4		0	0	1	0	0
8						0	0	4	3	3				0	0	4	2	2
9						TOTAL OF COLUMNS								ADJUSTED TO MAKE COLUMNS ALL EVEN
Sheet1

 

[Eric W]

Playing Nim?

My HTML Maker isn't working right, so here are the formulas:

F3:

=MID(DEC2BIN($D3,5),COLUMNS($F3:F3),1)+0

copy to F3:J7

L3:

=IFERROR(AGGREGATE(15,6,IF(MMULT(ISEVEN(MID(DEC2BIN(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,ROW(INDIRECT("1:"&D3-1))),1),D3)

L4:

=IF(SUMPRODUCT(($D$3:D3=$L$3:$L3)+0)=0,D4,IFERROR(AGGREGATE(15,6,IF(MMULT(ISEVEN(MID(DEC2BIN(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,ROW(INDIRECT("1:"&D4-1))),1),D4))

Copy to L5:L7.

The L4 formula is basically the same as L3, it just has a check at the start to make sure that none of the rows above it have changed. The L3 formula looks at all the numbers from 1 to D3-1, and sees if any of them work.

Of course, if all the totals are already even on the left table, there is no answer and nothing changes.

The formula works, but it's fairly complicated. You might be better off with a macro to do the same thing. Let me know if that's of interest, and I'll write it up.

 

[tezza]

LOL yes I am as it happens but that became secondary as I like the excel side of seeing how far I can get before asking for help

I copied your formulas across but it shows the same values in L as in D.

I tested in 2007 and 2010.

Any thoughts?

Regards
Terry

 

[tezza]

Ah got it, it's an array.

thank you so much for this, very much appreciated

There's new code in your formula to me so that'll give me something else to examine lol

If you wanted a better animation version look at 'Pearls of Swine 3'

EDIT:
I tested it on a few levels and it jumps the gun a little at times.

If you put in 4 6 7 it returns 1 3 2 which changes more than one line.

Regards
Tez

 

[Eric W]

I'm glad it works for you. I was playing around with it a bit more, and found some issues.

First, you should change the L4 formula to:

=IF(PRODUCT(($D$3:D3=$L$3:$L3)+0)=0,D4,IFERROR(AGGREGATE(15,6,IF(MMULT(ISEVEN(MID(DEC2BIN(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,ROW(INDIRECT("1:"&D4-1))),1),D4))

And confirm it with Control+Shift+Enter, since it's an array formula.


And I don't get to break out the MMULT very often, so it was fun using it this time! Hope you have fun figuring out how it works, let me know if you have questions.


Finally, I wrote up the macro, here it is:

Public Function SolveNim(ByVal vals As Range) As Variant
Dim i As Long, j As Long, k As Long, n As Long, b As String, a As Long, v As Variant
Dim tots(1 To 10) As Long, out() As Long

    v = vals.Value                          ' Get the current values
    ReDim out(1 To UBound(v), 1 To 1)       ' Create an output array
    For i = 1 To UBound(v)                  ' Save current values
        out(i, 1) = v(i, 1)
    Next i

    For i = 1 To UBound(v)                  ' Check each number
        For j = 1 To v(i, 1) - 1            '  From 1 to n-1
            Erase tots                      ' Clear binary subtotals
            For k = 1 To UBound(v)          ' Add up the binary totals for
                If k = i Then               '  this set of numbers
                    n = j
                Else
                    n = v(k, 1)
                End If
                b = WorksheetFunction.Dec2Bin(n, 10)
                For a = 1 To 10
                    tots(a) = tots(a) + Mid(b, a, 1)
                Next a
            Next k
            For a = 1 To 10                 ' All even?
                If tots(a) Mod 2 = 1 Then GoTo NextJ:
            Next a
            out(i, 1) = j                   ' Yes, save the changed one
            SolveNim = out
            Exit Function                   ' and quit
NextJ:
        Next j
    Next i
            
End Function

Enter that on a standard module. Then on your sheet select a range, say K3:K7, enter this formula:

=SolveNim(D3:D7)
and confirm with Control+Shift+Enter.

Good luck!

 

[tezza]

Very nice.

I put the updated formula in with the data 3 4 5 6 but the outcome was the same.

The VBA looks to be working great though

You're a star.

Thank you

 

[tezza]

Try 3 2 8 1 0 it throws up 0 0 0 0 0 in VBA and 3 2 8 1 0 with formulas but it's an odd binary.

 

[tezza]

Try 3 2 8 1 0 it throws up 0 0 0 0 0 in VBA and 3 2 8 1 0 with formulas but it's an odd binary.

3 1 12 13 0 seems to have an issue too, I'll leave it with you

 

[Eric W]

Sorry about that! I checked the values from 1 to n-1, when I should have included 0 to n-1. Here are the revised formulas:

L3:

=IFERROR(AGGREGATE(15,6,IF(MMULT(ISEVEN(MID(DEC2BIN(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,ROW(INDIRECT("1:"&D3))-1),1),D3)

L4:

=IF(PRODUCT(($D$3:D3=$L$3:$L3)+0)=0,D4,IFERROR(AGGREGATE(15,6,IF(MMULT(ISEVEN(MID(DEC2BIN(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,ROW(INDIRECT("1:"&D4))-1),1),D4))

with Control+Shift+Enter.

For the macro, just change this line:

For j = 1 To v(i, 1) - 1            '  From 1 to n-1

to

For j = [COLOR=#ff0000]0[/COLOR] To v(i, 1) - 1            '  From 0 to n-1