# Thread: Recalculate Split Binary Columns Thanks:  3 Post #5323688 (1)Post #5324344 (1)Post #5323675 (1) Likes:  3 Post #5323675 (1)Post #5323688 (1)Post #5324344 (1)

1. ## Re: Recalculate Split Binary Columns

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

Sheet1

 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

 Cell Formula F3 =MID(DEC2BIN(\$D3,5),COLUMNS(\$F3:F3),1)+0 G3 =MID(DEC2BIN(\$D3,5),COLUMNS(\$F3:G3),1)+0 H3 =MID(DEC2BIN(\$D3,5),COLUMNS(\$F3:H3),1)+0 I3 =MID(DEC2BIN(\$D3,5),COLUMNS(\$F3:I3),1)+0 J3 =MID(DEC2BIN(\$D3,5),COLUMNS(\$F3:J3),1)+0 K3 {=solvenim(D3:D7)} 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)} N3 =MID(DEC2BIN(\$K3,5),COLUMNS(\$N3:N3),1)+0 O3 =MID(DEC2BIN(\$K3,5),COLUMNS(\$N3:O3),1)+0 P3 =MID(DEC2BIN(\$K3,5),COLUMNS(\$N3:P3),1)+0 Q3 =MID(DEC2BIN(\$K3,5),COLUMNS(\$N3:Q3),1)+0 R3 =MID(DEC2BIN(\$K3,5),COLUMNS(\$N3:R3),1)+0 F4 =MID(DEC2BIN(\$D4,5),COLUMNS(\$F4:F4),1)+0 G4 =MID(DEC2BIN(\$D4,5),COLUMNS(\$F4:G4),1)+0 H4 =MID(DEC2BIN(\$D4,5),COLUMNS(\$F4:H4),1)+0 I4 =MID(DEC2BIN(\$D4,5),COLUMNS(\$F4:I4),1)+0 J4 =MID(DEC2BIN(\$D4,5),COLUMNS(\$F4:J4),1)+0 K4 {=solvenim(D3:D7)} 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))} N4 =MID(DEC2BIN(\$K4,5),COLUMNS(\$N4:N4),1)+0 O4 =MID(DEC2BIN(\$K4,5),COLUMNS(\$N4:O4),1)+0 P4 =MID(DEC2BIN(\$K4,5),COLUMNS(\$N4:P4),1)+0 Q4 =MID(DEC2BIN(\$K4,5),COLUMNS(\$N4:Q4),1)+0 R4 =MID(DEC2BIN(\$K4,5),COLUMNS(\$N4:R4),1)+0 F5 =MID(DEC2BIN(\$D5,5),COLUMNS(\$F5:F5),1)+0 G5 =MID(DEC2BIN(\$D5,5),COLUMNS(\$F5:G5),1)+0 H5 =MID(DEC2BIN(\$D5,5),COLUMNS(\$F5:H5),1)+0 I5 =MID(DEC2BIN(\$D5,5),COLUMNS(\$F5:I5),1)+0 J5 =MID(DEC2BIN(\$D5,5),COLUMNS(\$F5:J5),1)+0 K5 {=solvenim(D3:D7)} L5 {=IF(PRODUCT((\$D\$3:D4=\$L\$3:\$L4)+0)=0,D5,IFERROR(AGGREGATE(15,6,IF(MMULT(ISEVEN(MID(DEC2BIN(ROW(INDIRECT("1:"&D5))-1,5),{1,2,3,4,5},1)+\$F\$8:\$J\$8-F5:J5)+0,{1;1;1;1;1})=5,ROW(INDIRECT("1:"&D5))-1),1),D5))} N5 =MID(DEC2BIN(\$K5,5),COLUMNS(\$N5:N5),1)+0 O5 =MID(DEC2BIN(\$K5,5),COLUMNS(\$N5:O5),1)+0 P5 =MID(DEC2BIN(\$K5,5),COLUMNS(\$N5:P5),1)+0 Q5 =MID(DEC2BIN(\$K5,5),COLUMNS(\$N5:Q5),1)+0 R5 =MID(DEC2BIN(\$K5,5),COLUMNS(\$N5:R5),1)+0 F6 =MID(DEC2BIN(\$D6,5),COLUMNS(\$F6:F6),1)+0 G6 =MID(DEC2BIN(\$D6,5),COLUMNS(\$F6:G6),1)+0 H6 =MID(DEC2BIN(\$D6,5),COLUMNS(\$F6:H6),1)+0 I6 =MID(DEC2BIN(\$D6,5),COLUMNS(\$F6:I6),1)+0 J6 =MID(DEC2BIN(\$D6,5),COLUMNS(\$F6:J6),1)+0 K6 {=solvenim(D3:D7)} L6 {=IF(PRODUCT((\$D\$3:D5=\$L\$3:\$L5)+0)=0,D6,IFERROR(AGGREGATE(15,6,IF(MMULT(ISEVEN(MID(DEC2BIN(ROW(INDIRECT("1:"&D6))-1,5),{1,2,3,4,5},1)+\$F\$8:\$J\$8-F6:J6)+0,{1;1;1;1;1})=5,ROW(INDIRECT("1:"&D6))-1),1),D6))} N6 =MID(DEC2BIN(\$K6,5),COLUMNS(\$N6:N6),1)+0 O6 =MID(DEC2BIN(\$K6,5),COLUMNS(\$N6:O6),1)+0 P6 =MID(DEC2BIN(\$K6,5),COLUMNS(\$N6:P6),1)+0 Q6 =MID(DEC2BIN(\$K6,5),COLUMNS(\$N6:Q6),1)+0 R6 =MID(DEC2BIN(\$K6,5),COLUMNS(\$N6:R6),1)+0 F7 =MID(DEC2BIN(\$D7,5),COLUMNS(\$F7:F7),1)+0 G7 =MID(DEC2BIN(\$D7,5),COLUMNS(\$F7:G7),1)+0 H7 =MID(DEC2BIN(\$D7,5),COLUMNS(\$F7:H7),1)+0 I7 =MID(DEC2BIN(\$D7,5),COLUMNS(\$F7:I7),1)+0 J7 =MID(DEC2BIN(\$D7,5),COLUMNS(\$F7:J7),1)+0 K7 {=solvenim(D3:D7)} L7 {=IF(PRODUCT((\$D\$3:D6=\$L\$3:\$L6)+0)=0,D7,IFERROR(AGGREGATE(15,6,IF(MMULT(ISEVEN(MID(DEC2BIN(ROW(INDIRECT("1:"&D7))-1,5),{1,2,3,4,5},1)+\$F\$8:\$J\$8-F7:J7)+0,{1;1;1;1;1})=5,ROW(INDIRECT("1:"&D7))-1),1),D7))} N7 =MID(DEC2BIN(\$K7,5),COLUMNS(\$N7:N7),1)+0 O7 =MID(DEC2BIN(\$K7,5),COLUMNS(\$N7:O7),1)+0 P7 =MID(DEC2BIN(\$K7,5),COLUMNS(\$N7:P7),1)+0 Q7 =MID(DEC2BIN(\$K7,5),COLUMNS(\$N7:Q7),1)+0 R7 =MID(DEC2BIN(\$K7,5),COLUMNS(\$N7:R7),1)+0 F8 =SUM(F3:F7) G8 =SUM(G3:G7) H8 =SUM(H3:H7) I8 =SUM(I3:I7) J8 =SUM(J3:J7) N8 =SUM(N3:N7) O8 =SUM(O3:O7) P8 =SUM(P3:P7) Q8 =SUM(Q3:Q7) R8 =SUM(R3:R7)
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

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 = 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```

2. ## Re: Recalculate Split Binary Columns

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

3. ## Re: Recalculate Split Binary Columns

@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.

4. ## Re: Recalculate Split Binary Columns

Originally Posted by 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.
Hi

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

5. ## Re: Recalculate Split Binary Columns

Originally Posted by tezza
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

6. ## Re: Recalculate Split Binary Columns

Originally Posted by Peter_SSs
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

7. ## Re: Recalculate Split Binary Columns

Originally Posted by tezza
Ah I see, I didn't adjust the range to analyse. You live and learn
Cheers.

8. ## Re: Recalculate Split Binary Columns

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

9. ## Re: Recalculate Split Binary Columns

Originally Posted by 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.
Calculations are automatic, would it be due to an older version of excel?

10. ## Re: Recalculate Split Binary Columns

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.