CP018: Dont be a Pivot Table Virgin!

26 Responses to “Get busy this weekend, with OR XOR AND [Excel Homework]”

1. Stef@n says:

   July 29, 2011 at 11:48 am

   first solution for AND
   The two numbers are in A1 and B1
   = SUBSTITUTE (SUBSTITUTE (A1+B1*9*9, 9, 1), 8, 0)
   regards
   Stef@n

   Reply
2. Stef@n says:

   July 29, 2011 at 11:50 am

   next solution for OR
   =1*SUBSTITUTE (A1+A2;2;1)

   regards
   Stef@n

   Reply
3. Stef@n says:

   July 29, 2011 at 11:52 am

   last solution for XOR
   =1*SUBSTITUTE (A1+A2;2;0)

   regards
   Stef@n

   Reply
4. Irvine says:

   July 29, 2011 at 12:26 pm

   Or you could make use of the VBA logical operators!

   Define the following as custom functions
   Public Function BITXOR(x As Long, y As Long)
   BITXOR = x Xor y
   End Function

   Public Function BITAND(x As Long, y As Long)
   BITAND = x And y
   End Function

   Public Function BITOR(x As Long, y As Long)
   BITOR = x Or y
   End Function

   and then use them such:
   A B =BITOR(A,B) =BITAND(A,B) =BITXOR(A,B)
   0101 0100 0101 0100 0001

   Reply
5. Stef@n says:

   July 29, 2011 at 12:46 pm

   an another solution for AND
   =1*SUBSTITUTE (SUBSTITUTE (A1+A2;1;0);2;1)

   note:
   the binary numbers are in A1 and A2 !
   regards
   Stef@n

   Reply
6. Shair says:

   July 29, 2011 at 2:22 pm

   I was obviously playing hooky at the beach during the bit-wise math lesson – you lost me at “Understanding bit-wise operations” 🙂

   Reply
7. Anup Agarwal says:

   July 30, 2011 at 8:46 am

   After looking at the above solutions, I find my solution silly, but still:

   For the following formulae,
   Row 1: headers,
   Row 2: OR
   Row 3: AND
   Row 4: XOR

   Column 1: Input 1
   Column 2: Input 2
   Column 3: Result

   OR
   {=SUM(IF(MID(A2,ROW(OFFSET($A$1,0,0,LEN(A2),1)),1)+MID(B2,ROW(OFFSET($A$1,0,0,LEN(B2),1)),1)>0,1,0)*10^(LEN(A2)-ROW(OFFSET($A$1,0,0,LEN(B2),1))))}

   AND
   {=SUM(IF(MID(A3,ROW(OFFSET($A$1,0,0,LEN(A3),1)),1)+MID(B3,ROW(OFFSET($A$1,0,0,LEN(B3),1)),1)=2,1,0)*10^(LEN(A3)-ROW(OFFSET($A$1,0,0,LEN(B3),1))))}

   XOR
   {=SUM(IF(MID(A4,ROW(OFFSET($A$1,0,0,LEN(A4),1)),1)+MID(B4,ROW(OFFSET($A$1,0,0,LEN(B4),1)),1)=1,1,0)*10^(LEN(A4)-ROW(OFFSET($A$1,0,0,LEN(B4),1))))}

   Reply
8. Hui... says:

   July 31, 2011 at 11:57 pm

   @Anup
   Please don't consider your solution silly
   Firstly, You are the 3rd person to submit an answer
   Secondly, The best formula/function is the one that you know and understand.

   Reply
9. Michael Azer says:

   August 1, 2011 at 1:40 pm

   I think I have a very tedious solution, which people won't have the patience to do except in small numbers.

   I used the same problem setup as "Anup Agarwal"

   AND =IF(AND(MID(B2,1,1)="1",MID(C2,1,1)="1"),1,0)&IF(AND(MID(B2,2,1)="1",MID(C2,2,1)="1"),1,0)&IF(AND(MID(B2,3,1)="1",MID(C2,3,1)="1"),1,0)&IF(AND(MID(B2,4,1)="1",MID(C2,4,1)="1"),1,0)

   OR =IF(OR(MID(B3,1,1)="1",MID(C3,1,1)="1"),1,0)&IF(OR(MID(B3,2,1)="1",MID(C3,2,1)="1"),1,0)&IF(OR(MID(B3,3,1)="1",MID(C3,3,1)="1"),1,0)&IF(OR(MID(B3,4,1)="1",MID(C3,4,1)="1"),1,0)

   =IF(OR(AND(MID(B4,1,1)="1",MID(C4,1,1)="0"),AND(MID(B4,1,1)="0",MID(C4,1,1)="1")),1,0)&IF(OR(AND(MID(B4,2,1)="1",MID(C4,2,1)="0"),AND(MID(B4,2,1)="0",MID(C4,2,1)="1")),1,0)&IF(OR(AND(MID(B4,3,1)="1",MID(C4,3,1)="0"),AND(MID(B4,3,1)="0",MID(C4,3,1)="1")),1,0)&IF(OR(AND(MID(B4,4,1)="1",MID(C4,4,1)="0"),AND(MID(B4,4,1)="0",MID(C4,4,1)="1")),1,0)

   Reply
10. Michael Azer says:

    August 1, 2011 at 1:43 pm

    Sorry my last post was totally messed up

    AND
    =IF(AND(MID(B2,1,1)="1",MID(C2,1,1)="1"),1,0)&IF(AND(MID(B2,2,1)="1",MID(C2,2,1)="1"),1,0)&IF(AND(MID(B2,3,1)="1",MID(C2,3,1)="1"),1,0)&IF(AND(MID(B2,4,1)="1",MID(C2,4,1)="1"),1,0)
    OR
    =IF(OR(MID(B3,1,1)="1",MID(C3,1,1)="1"),1,0)&IF(OR(MID(B3,2,1)="1",MID(C3,2,1)="1"),1,0)&IF(OR(MID(B3,3,1)="1",MID(C3,3,1)="1"),1,0)&IF(OR(MID(B3,4,1)="1",MID(C3,4,1)="1"),1,0)
    XOR
    =IF(OR(AND(MID(B4,1,1)="1",MID(C4,1,1)="0"),AND(MID(B4,1,1)="0",MID(C4,1,1)="1")),1,0)&IF(OR(AND(MID(B4,2,1)="1",MID(C4,2,1)="0"),AND(MID(B4,2,1)="0",MID(C4,2,1)="1")),1,0)&IF(OR(AND(MID(B4,3,1)="1",MID(C4,3,1)="0"),AND(MID(B4,3,1)="0",MID(C4,3,1)="1")),1,0)&IF(OR(AND(MID(B4,4,1)="1",MID(C4,4,1)="0"),AND(MID(B4,4,1)="0",MID(C4,4,1)="1")),1,0)

    Reply
11. Stephen says:

    August 1, 2011 at 1:45 pm

    @stefan,
    I just couldn't get your solutions to work.
    01010101010 + 01010101110 = 02020210120
    what am i doing wrong?

    Reply
12. Stephen says:

    August 1, 2011 at 1:56 pm

    @anup
    ...I got yours to work!

    Reply
13. Rich says:

    August 1, 2011 at 8:17 pm

    @Stephen - I get the same, but Stef@an's second solution for AND does work (at least for the test cases I used)

    Reply
14. Stef@n says:

    August 2, 2011 at 9:09 am

    @ Stephen / Rich
    yes , you are right ! - only this works:

    OR
    =1*SUBSTITUTE (A1+A2;2;1)
    XOR
    =1*SUBSTITUTE (A1+A2;2;0)
    AND
    =1*SUBSTITUTE (SUBSTITUTE (A1+A2;1;0);2;1)

    Reply
15. Michael Azer says:

    August 3, 2011 at 1:36 pm

    @Stef@n - You're answer is really smart, I never knew about the substitute function before. Great Work!

    Reply
16. Stef@n says:

    August 3, 2011 at 2:45 pm

    Thx Michael 🙂
    yes - it is simply easy 😉
    if you add 1 and 1 - excel calculate 2
    and then you have to substitute the 2 - new = 0 respectively 1

    Reply
17. Rob says:

    September 11, 2011 at 4:16 pm

    Here is a good resource for people wanting to learn binary and hexadecimal.
    http://justwebware.com/bitwise/bitwise.html

    Reply
18. Kyle McGhee says:

    January 29, 2012 at 3:57 pm

    Three that weren't asked for:
    NOT
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1+A2,0,3),1,0),3,1)

    EQV
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1+A2,0,3),2,3),1,0),3,1)

    IMP
    =SUBSTITUTE(SUBSTITUTE(A1+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,3),1,0),3,1),0,1),2,0)

    (was using Daniel Ferry's bitwise file to verify against)

    Reply
19. Q says:

    February 27, 2012 at 9:05 am

    @ Kyle

    Not only takes one parameter and inverts 0 -1 and 1-0

    Took out the +A2
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,3),1,0),3,1)

    Reply
20. Richard Tallent says:

    November 1, 2012 at 3:57 pm

    Great solutions!

    I'll add two:

    NAND =1*SUBSTITUTE (A1+A2,2,0)

    NOR=1*SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(A1+A2,0,2),1,0),2,1)

    Reply
21. John Fuller says:

    August 29, 2014 at 4:15 pm

    This will work for binary numbers of any size (although the text format mask will have to have as many zeroes as there are digits in the longest addend)

    Assume binary #s are in C35 & C36, then add and format as text in C37:
    =TEXT(C36+C35,"000000000000")
    -sum- = 101112211112

    AND - SUBSTITUTE 0s for 1s in -sum-, then sub 1s for 2s
    =SUBSTITUTE(SUBSTITUTE(C37,"1","0"),"2","1")

    OR - sub 1s for 2s in -sum-
    =SUBSTITUTE(C37,"2","1")

    XOR - sub 0s for 2s in -sum-
    =SUBSTITUTE(C37,"2","0")

    Reply
22. Shane says:

    September 21, 2014 at 3:47 am

    Just wandered by:

    AND:

    =SUBSTITUTE(A1+A2,1,0)/2

    Reply
    • xlpadawan says:

      September 22, 2014 at 2:22 pm

      Clever, Shane. I like that.

      Reply
23. How do I perform a XOR calucation of two binary numbers in excel 2007 - QuestionFocus says:

    December 9, 2017 at 2:27 pm

    […] post http://www.excelhero.com/blog/2010/01/5-and-3-is-1.html for examples using Sumproduct, and http://chandoo.org/wp/2011/07/29/bitwise-operations-in-excel/ for examples using Text […]

    Reply
24. Ronald Vonk says:

    December 6, 2018 at 12:00 pm

    Hi Chandoo,

    I am not (yet) really into bitwise calculation, but I am looking for a way to speed up my vba calculation with very big numbers. Would is ben convenient to use bitwise notation for this?

    Best regards,

    Ronald (the Netherlands)
    p.s. love your country!

    Reply
    • Hui... says:

      December 7, 2018 at 4:25 am

      @Ronald

      I'd suggest asking this in the Chandoo.org Forums
      https://chandoo.org/forum/

      Attach a sample file with an example of some data and describe what you want to achieve

      Reply

Leave a Reply