# Gray and 2's complement with Excel

1. Feb 8, 2008

### Clutch Cargo

Does anyone know how I can get Excel to convert binary to Gray Code and two's complement?

2. Oct 22, 2009

### zgozvrm

Here is the procedure for converting from binary to gray code.

Assume an n-digit binary number $$b_nb_{n-1}...b_2b_1b_0$$.
The gray code equivalent is found by the following formula:
$$g_n = b_n$$, for all other digits, $$g_x = b_x$$ XOR $$b_{x+1}$$

3. Nov 21, 2009

### zgozvrm

Assume an n-digit binary number $$b_nb_{n-1}...b_2b_1$$.
Since having a $$b_0$$ 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

4. Nov 23, 2009

### zgozvrm

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 5. Nov 23, 2009 ### zgozvrm 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.

6. Nov 24, 2009

### zgozvrm

(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:

• ###### Bin to Gray.xls
File size:
13.5 KB
Views:
767
7. Nov 24, 2009

### zgozvrm

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:

• ###### Bin to 2Comp.xls
File size:
14 KB
Views:
684
8. Dec 15, 2009

### mugaliens

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.

9. Dec 15, 2009

### zgozvrm

Congratulations, but you're a little late!

Gray code has been used at least as far back as 1878 in telegraph applications.

10. Nov 3, 2011

### chuckc

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

11. Nov 9, 2011

### zgozvrm

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