Recalculate Split Binary Columns
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 25

Thread: Recalculate Split Binary Columns

  1. #11
    Board Regular
    Join Date
    Sep 2006
    Posts
    154
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Recalculate Split Binary Columns

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


    Sheet1

    DEFGHIJKLMNOPQR
    2Adjust here 168421VBAAdjusted but errors 168421
    33 00011#NAME?3EXAMPLE#NAME?#NAME?#NAME?#NAME?#NAME?
    41 00001#NAME?1#NAME?#NAME?#NAME?#NAME?#NAME?
    512 01100#NAME?12#NAME?#NAME?#NAME?#NAME?#NAME?
    613 01101#NAME?13#NAME?#NAME?#NAME?#NAME?#NAME?
    70 00000#NAME?0#NAME?#NAME?#NAME?#NAME?#NAME?
    8 02213 #NAME?#NAME?#NAME?#NAME?#NAME?
    9 TOTAL OF COLUMNS ADJUSTED TO MAKE COLUMNS ALL EVEN

    Spreadsheet Formulas
    CellFormula
    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
    Office 2007 user.

  2. #12
    Board Regular
    Join Date
    Sep 2006
    Posts
    154
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Office 2007 user.

  3. #13
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,358
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default 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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #14
    Board Regular
    Join Date
    Sep 2006
    Posts
    154
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Recalculate Split Binary Columns

    Quote Originally Posted by Peter_SSs View Post
    @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.
    Office 2007 user.

  5. #15
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,358
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Recalculate Split Binary Columns

    Quote Originally Posted by tezza View Post
    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 by Peter_SSs; Aug 12th, 2019 at 07:25 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #16
    Board Regular
    Join Date
    Sep 2006
    Posts
    154
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Recalculate Split Binary Columns

    Quote Originally Posted by Peter_SSs View Post
    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
    Office 2007 user.

  7. #17
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,358
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Recalculate Split Binary Columns

    Quote Originally Posted by tezza View Post
    Ah I see, I didn't adjust the range to analyse. You live and learn
    Cheers.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #18
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,474
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  9. #19
    Board Regular
    Join Date
    Sep 2006
    Posts
    154
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Recalculate Split Binary Columns

    Quote Originally Posted by Eric W View Post
    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?
    Office 2007 user.

  10. #20
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,474
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •