Recalculate Split Binary Columns

tezza

Board Regular
Joined
Sep 10, 2006
Messages
165
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
 

Some videos you may like

Excel Facts

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

tezza

Board Regular
Joined
Sep 10, 2006
Messages
165
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
Joined
May 28, 2005
Messages
45,214
Office Version
365
Platform
Windows
@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
Joined
Sep 10, 2006
Messages
165
@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
Joined
May 28, 2005
Messages
45,214
Office Version
365
Platform
Windows
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
Joined
Sep 10, 2006
Messages
165
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 :)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,910
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
Joined
Sep 10, 2006
Messages
165
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
Joined
Aug 18, 2015
Messages
9,910
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,450
Messages
5,486,969
Members
407,575
Latest member
calc

This Week's Hot Topics

Top