# Recalculate Split Binary Columns

#### tezza

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

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

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### tezza

##### Board Regular
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

##### MrExcel MVP, Moderator
@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

##### Board Regular
@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

##### MrExcel MVP, Moderator
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

Last edited:

#### tezza

##### Board Regular
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

##### MrExcel MVP, Moderator
Ah I see, I didn't adjust the range to analyse. You live and learn
Cheers.

#### Eric W

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
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.