Gray and 2's complement with Excel

[Clutch Cargo]

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

 

[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}$$

 

[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}$$

That should have read:
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

 

[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

 

[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.

 

[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

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.

 

[zgozvrm]

To convert to 2's complement in Excel:

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.

 

[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.

 

[zgozvrm]

Congratulations, but you're a little late!

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

 

[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

 

[zgozvrm]

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

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