Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Gray and 2's complement with Excel

  1. Feb 8, 2008 #1
    Does anyone know how I can get Excel to convert binary to Gray Code and two's complement?
     
  2. jcsd
  3. Oct 22, 2009 #2
    Here is the procedure for converting from binary to gray code.

    Assume an n-digit binary number [tex]b_nb_{n-1}...b_2b_1b_0[/tex].
    The gray code equivalent is found by the following formula:
    [tex]g_n = b_n[/tex], for all other digits, [tex]g_x = b_x[/tex] XOR [tex]b_{x+1}[/tex]
     
  4. Nov 21, 2009 #3
    That should have read:
    Assume an n-digit binary number [tex]b_nb_{n-1}...b_2b_1[/tex].
    Since having a [tex]b_0[/tex] term would mean we have n+1 digits.


    Example of conversion:
    Given binary code 01001, convert to gray code like this:
    1) there are 5 digits (n=5), so we have b5=0, b4=1, b3=0, b2=0, b1=1
    2) g5=b5 = 0
    3) g4 = b4 XOR b5 = 1 XOR 0 = 1
    4) g3 = b3 XOR b4 = 0 XOR 1 = 1
    5) g2 = b2 XOR b3 = 0 XOR 0 = 0
    6) g1 = b1 XOR b2 = 1 XOR 0 = 1
    7) gray code equivalent of 01001 = 01101
     
  5. Nov 23, 2009 #4
    As for doing this in Excel...

    1) Enter the formula =MID(A$1,ROW()-2,1) in cells A3 through A12
    2) Enter the formula =CONCATENATE(B3,B4,B5,B6,B7,B8,B9,B10,B11,B12) in cell B1
    3) Enter the formula =IF(A3="","",IF(A3=A2,"0","1")) in cell B3
    4) Select cell B3, copy, select cells B4 through B12, paste

    Enter your binary number (up to 10 digits) in cell A1
    The Gray code equivalent will be displayed in cell B1
     
  6. Nov 23, 2009 #5
    To convert to 2's complement in Excel:

    1) Enter the formula =MID(A$1,ROW()-2,1) in cells A3 through A12
    2) Enter the formula =CONCATENATE(E3,E4,E5,E6,E7,E8,E9,E10,E11,E12) in cell E1
    3) Enter the formula =IF(A3="","",1-A3) in cell B3
    4) Enter the formula =IF(B3="","",IF(B4="",B3+1,B3+D4)) in cell C3
    5) Enter the formula =IF(C3="","",IF(C3>1,1,0)) in cell D3
    6) Enter the formula =IF(B3="","",IF(C3=2,0,C3)) in cell E3
    7) Select cells B3 through E3, copy, select cells B4 through E12, paste

    Enter your binary number (up to 10 digits) in cell A1
    The 2's complement will be displayed in cell E1


    A MUCH simpler way would be to install the Analysis ToolPak Add-In (included with Excel) and use the functions BIN2DEC and DEC2BIN which convert binary to decimal and decimal to binary, respectively. In this case enter the following:
    1) The formula =BIN2DEC(-DEC2BIN(A1)) in cell B2

    Enter your binary number (up to 10 digits) in cell A1
    The 2's complement will be displayed in cell B1

    The only problem with this method, is that you are limited to 10 binary digits.

    In the first method, you can extend past 10 binary digits by copying the formulas further down the spreadsheet, and then adding more cells to the CONCATENATE formula in cell E1.

    This can be done much easier in VBA, but that's not really having Excel do the conversion, like you asked.
     
  7. Nov 24, 2009 #6
    Here's an actual spreadsheet...
    (I moved all B-cell formulas to C-cells)

    Enter your binary number in the yellow box (up to 10 digits).
    The Gray code equivalent is displayed in the green box.
     

    Attached Files:

  8. Nov 24, 2009 #7
    Here's a spreadsheet ...

    Again, enter your binary number (up to 10 digits) in the yellow box.
    The 2's complement is displayed in the green box.
     

    Attached Files:

  9. Dec 15, 2009 #8
    Gee! That's what you call it. "Gray code." I thought of this as a way of detecting errors in bit streams about five years ago, but never finished playing around with it.
     
  10. Dec 15, 2009 #9
    Congratulations, but you're a little late!

    Gray code has been used at least as far back as 1878 in telegraph applications.
     
  11. Nov 3, 2011 #10
    I tried the Excel method above but when I put in a value of 0, it says the Gray Code equivalent is 1. And when I put in a value of 1, it still says the Gray Code equivalent is 1. Obviously there's something wrong there
     
  12. Nov 9, 2011 #11
    Good catch. Here's the fix...

    1) Set the format of cell A1 to text
    Select cell A1
    Click Format -> Cells...
    Select the "Number" tab (if not already chosen)
    Click "Text" in the Category list
    Click OK
    2) Enter the formula =MID(A$1,ROW()-2,1) in cells A3 through A12
    3) Enter the formula =CONCATENATE(B3,B4,B5,B6,B7,B8,B9,B10,B11,B12) in cell B1
    4) Enter the formula =A3 in cell B3
    5) Enter the formula =IF(A4="","",IF(A4=A3,"0","1")) in cell B4
    6) Select cell B4, copy, select cells B5 through B12, paste
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?



Similar Discussions: Gray and 2's complement with Excel
  1. 2's complement in VB (Replies: 2)

  2. Excel Macros (Replies: 3)

  3. An Excel question (Replies: 4)

  4. Method of Complements (Replies: 3)

Loading...