Recalculate Split Binary Columns

tezza

Active Member
Joined
Sep 10, 2006
Messages
375
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
  2. Web
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.


Code:
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Playing Nim? :biggrin:

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

F3:
Code:
=MID(DEC2BIN($D3,5),COLUMNS($F3:F3),1)+0
copy to F3:J7

L3:
Code:
=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:
Code:
=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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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:

Code:
=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:

Code:
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!
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0
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:
Code:
=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:
Code:
=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:

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

to

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

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top