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]

Not sure what's going on now but this is where I'm at:

Excel Workbook
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	Adjust here		16	8	4	2	1	VBA	Adjusted but errors		16	8	4	2	1
3	3		0	0	0	1	1	#NAME?	3	EXAMPLE	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
4	1		0	0	0	0	1	#NAME?	1		#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
5	12		0	1	1	0	0	#NAME?	12		#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
6	13		0	1	1	0	1	#NAME?	13		#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
7	0		0	0	0	0	0	#NAME?	0		#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
8			0	2	2	1	3				#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
9			TOTAL OF COLUMNS								ADJUSTED TO MAKE COLUMNS ALL EVEN
Sheet1

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 = 0 To v(i, 1) - 1            '  From 0 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

 

[tezza]

Oh hang on, my macro was disabled, the VBA seems good now but not the formula, which isn't important but there as a challenge if you want to sort it lol

 

[Peter_SSs]

@tezza
When using Excel jeanie, consider using the ‘Analyse range (Forum)’ field near the top left to restrict the number of formulas generated. There is generally no need to display multiple formulas that are basically the same, it just fills up the board and makes your post and the thread hard to read/navigate.

 

[tezza]

@tezza
When using Excel jeanie, consider using the ‘Analyse range (Forum)’ field near the top left to restrict the number of formulas generated. There is generally no need to display multiple formulas that are basically the same, it just fills up the board and makes your post and the thread hard to read/navigate.

Hi

I just tried your suggestion and it doesn't appear to have made any difference to what gets posted.

 

[Peter_SSs]

Hi

I just tried your suggestion and it doesn't appear to have made any difference to what gets posted.

Perhaps you didn't do it quite right. For the sheet shown in post 11 ..
1. In the actual worksheet, select the range D2:R9
2. Invoke Excel jeanie
3. On the 'Forum' tab (the only one to use) you should see$D$2:$R$9 in the 'Range' box. Leave it there and..
4. Click in the 'Analyse range (Forum)' box
5. On you actual worksheet, select say J3:L3
6. Click the 'Forum Standard' button
7. Paste into your test thread in the Test Here forum. You should have just 3 formulas shown.

N.B.
If you want to post a few formulas from disjoint ranges, you used to be able to use Ctrl+Click in that 'Analyse range (Forum)' box but with a recent Windows update that doesn't work any more, at least for me. However, in the 'Analyse range' box you can just type the cells you want formulas etc for separated by commas. eg F3, Q4, AA2080

 

[tezza]

Perhaps you didn't do it quite right. For the sheet shown in post 11 ..
1. In the actual worksheet, select the range D2:R9
2. Invoke Excel jeanie
3. On the 'Forum' tab (the only one to use) you should see$D$2:$R$9 in the 'Range' box. Leave it there and..
4. Click in the 'Analyse range (Forum)' box
5. On you actual worksheet, select say J3:L3
6. Click the 'Forum Standard' button
7. Paste into your test thread in the Test Here forum. You should have just 3 formulas shown.

N.B.
If you want to post a few formulas from disjoint ranges, you used to be able to use Ctrl+Click in that 'Analyse range (Forum)' box but with a recent Windows update that doesn't work any more, at least for me. However, in the 'Analyse range' box you can just type the cells you want formulas etc for separated by commas. eg F3, Q4, AA2080

Ah I see, I didn't adjust the range to analyse. You live and learn

 

[Peter_SSs]

Ah I see, I didn't adjust the range to analyse. You live and learn

Cheers.

 

[Eric W]

The formulas appear to be correct. Perhaps you have the Calculation Mode set to manual? Click Formulas > Calculation Options > Automatic and see what happens.

 

[tezza]

The formulas appear to be correct. Perhaps you have the Calculation Mode set to manual? Click Formulas > Calculation Options > Automatic and see what happens.

Calculations are automatic, would it be due to an older version of excel?

 

[Eric W]

Yeah, that's it. Somehow I thought you had them working. But both AGGREGATE and IFERROR came in Excel 2010. Rewriting the formulas without those made them a bit longer.

Try:

L3: =IF(ISERROR(LOOKUP(2,1/(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)),D3,LOOKUP(2,1/(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))

L4: =IF(SUMPRODUCT(--(D$3:D3=L$3:L3))=ROWS(D$3:D3),IF(ISERROR(LOOKUP(2,1/(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)),D4,LOOKUP(2,1/(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)),D4)

Neither one requires CSE entry.