Gray and 2's complement with Excel

  • Thread starter Clutch Cargo
  • Start date
  • Tags
    Excel
In summary: Enter your binary number (up to 10 digits) in cell A1The Gray code equivalent will be displayed in cell B1And here's the spreadsheet...Enter your binary number in the yellow box (up to 10 digits).The Gray code equivalent is displayed in the green box.In summary, to convert binary to Gray code and two's complement in Excel, you can use a combination of formulas and the Analysis ToolPak Add-In. The Gray code equivalent is found by using the formula g_n = b_n, and for all other digits, g_x = b_x XOR b_{x+1}. To convert to 2's complement, you can use the formula =BIN2DEC(-DEC2BIN
  • #1
Clutch Cargo
18
0
Does anyone know how I can get Excel to convert binary to Gray Code and two's complement?
 
Technology news on Phys.org
  • #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]
 
  • #3
zgozvrm said:
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]

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
 
  • #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
 
  • #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.
 
  • #6
zgozvrm said:
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.
 

Attachments

  • Bin to Gray.xls
    13.5 KB · Views: 1,444
  • #7
zgozvrm said:
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.
 

Attachments

  • Bin to 2Comp.xls
    14 KB · Views: 1,168
  • #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.
 
  • #9
Congratulations, but you're a little late!

Gray code has been used at least as far back as 1878 in telegraph applications.
 
  • #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
 
  • #11
chuckc said:
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
 

Related to Gray and 2's complement with Excel

1. What is the difference between gray code and 2's complement?

Gray code is a binary number system where only one bit changes between two consecutive numbers, while 2's complement is a method for representing negative numbers in binary form by taking the complement of the positive number and adding 1. In gray code, the most significant bit remains unchanged when a number is incremented, while in 2's complement, the most significant bit represents the sign of the number.

2. How do I convert a number from gray code to 2's complement in Excel?

To convert a number from gray code to 2's complement in Excel, you can use the following formula: =BIN2DEC(BITXOR(HEX2BIN(A1,4),HEX2BIN(A1,4))) where A1 is the cell containing the gray code number. This formula converts the gray code to binary, performs a bitwise XOR operation with the same binary number, and then converts the result back to decimal to get the 2's complement.

3. Can I use Excel to perform arithmetic operations on gray code and 2's complement numbers?

Yes, Excel has built-in functions for performing arithmetic operations on binary and hexadecimal numbers. You can use the BIN2DEC function to convert the numbers to decimal, perform the desired operation, and then convert the result back to binary or hexadecimal using the DEC2BIN or DEC2HEX functions.

4. How do I check if a number is in gray code or 2's complement form in Excel?

In Excel, you can use the ISTEXT and ISNUMBER functions to check if a cell contains a text or numerical value, respectively. If a cell contains a number, you can use the BITAND function to check if the number is a power of 2, which is a characteristic of gray code numbers. If the result is TRUE, then the number is in gray code form. Similarly, you can use the BITOR function to check if the number is a power of 2 plus 1, which is a characteristic of 2's complement numbers.

5. Are there any limitations to using Excel for working with gray code and 2's complement numbers?

While Excel can be a useful tool for working with binary and hexadecimal numbers, it has limitations when it comes to working with large numbers. Excel can only handle numbers up to a certain number of digits, and it may round off or display incorrect results for extremely large or small numbers. Additionally, Excel may not be as efficient as other programming languages for performing complex operations on binary and hexadecimal numbers.

Similar threads

How to disable access to old data with newest version of software?
    • Programming and Computer Science
Replies
8
Views
714
Comp Sci How can 1's complement, and 2's complement have different ranges?
    • Engineering and Comp Sci Homework Help
Replies
1
Views
648
Python How to resize and save a slice from a nii file as a PNG image in Python?
    • Programming and Computer Science
Replies
3
Views
2K
Extracting data from a spectrometer to Excel
    • Programming and Computer Science
Replies
7
Views
5K
Unsigned Subtraction in Modern Computers: Exploring the Method of Complements
    • Programming and Computer Science
Replies
5
Views
1K
A ##\Lambda##-enhanced gray molasses cooling
    • Atomic and Condensed Matter
Replies
0
Views
810
Difference between interpreted and compiled files....
    • Programming and Computer Science
Replies
29
Views
2K
Why is Excel telling me the GCD of 1.13*100 and 100 is 4?
    • Programming and Computer Science
Replies
4
Views
1K
Binary to Gray Conversion Program
    • Programming and Computer Science
Replies
6
Views
2K
MHB How Do You Correctly Subtract Using 1's and 2's Complement in Binary?
    • Programming and Computer Science
Replies
1
Views
1K

Hot Threads

Recent Insights

Back
Top