# Decimal to Hexadecimal to Binary with a twist

#### clea.patlecxerm

##### New Member
Hi,

I had used the DEC2HEX to convert a column of decimal numbers anywhere from 0 to 1023. It was ok and I got values from 000 to 3FF corresponding to 0000 0000 0000 and 0011 1111 1111 in binary. My problem now is when it is from -1 to -1023. The values I got are from FFFFFFFFFF to FFFFFFFC01.

Instead of this number: I should get, 401 to 7FF. In binary: 0100 0000 0001 to 0111 1111 1111.

The 11th bit will serve as the sign bit.

DEC HEX BINARY
0 000 0000 0000 0000 (ZERO)
+1 001 0000 0000 0001 (0 as the 11th bit means positive)
-1 401 0100 0000 0001 (1 as the 11th bit means a negative)
+1023 3FF 0011 1111 1111 (+ sign bit)
-1023 7FF 0111 1111 1111 (- sign bit)

For both positive and negative decimal number, I was thinking of getting the absolute value first, convert via DEC2HEX then just change the 11th bit according to the sign.

Your ideas will be greatly appreciated.

Thank you.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
If it works for you it works for you. Not sure what else to write. Just keep in mind that the typical computer uses 4 bits at a time; so 11 would be an odd choice. Also, 2s complement is more the norm for negative numbers rather than a sign bit approach.
Hi,

I had used the DEC2HEX to convert a column of decimal numbers anywhere from 0 to 1023. It was ok and I got values from 000 to 3FF corresponding to 0000 0000 0000 and 0011 1111 1111 in binary. My problem now is when it is from -1 to -1023. The values I got are from FFFFFFFFFF to FFFFFFFC01.

Instead of this number: I should get, 401 to 7FF. In binary: 0100 0000 0001 to 0111 1111 1111.

The 11th bit will serve as the sign bit.

DEC HEX BINARY
0 000 0000 0000 0000 (ZERO)
+1 001 0000 0000 0001 (0 as the 11th bit means positive)
-1 401 0100 0000 0001 (1 as the 11th bit means a negative)
+1023 3FF 0011 1111 1111 (+ sign bit)
-1023 7FF 0111 1111 1111 (- sign bit)

For both positive and negative decimal number, I was thinking of getting the absolute value first, convert via DEC2HEX then just change the 11th bit according to the sign.

Your ideas will be greatly appreciated.

Thank you.

Hi Tusharm,

Actually the last 4 bits is still treated as a single entity, that's why instead of 001 hex for positive, it's 401 hex for negative, the 4 being represented 0100. These data are actually being inputted to a Digital to Analog circuit. If the sign bit is 0, the output is positive voltage. If the sign bit is 1, the output is negative voltage. So 2s complement just won't work.

My problem is in implementing the procedure I outlined above. Sad to say, my experience with excel is very limited and for VBA is nil. I did went through the excel help regarding decimal, hexadecimal and binary; that's why I found out about DEC2HEX.

Hi

Try:

=TEXT(SUMPRODUCT(HEX2BIN(MID(DEC2HEX(A1+(A1<0)*(1024-2*A1),3),{3,2,1},1))*{1,10000,100000000}),REPT(0,12))

A more direct translation of your requirements...

Suppose the decimal number you have is in G14. Then, use the formula
=IF(G14<0,"01","00")&DEC2BIN(INT(ABS(G14)/512),1)&DEC2BIN(MOD(ABS(G14),512),9)

If you want to see a pattern of 4 binary digits separated by a space, convert the above to a number and use the custom format 0000 0000 0000

=--(IF(G14<0,"01","00")&DEC2BIN(INT(ABS(G14)/512),1)&DEC2BIN(MOD(ABS(G14),512),9))

The reason for breaking up the conversion into 2 parts is that DEC2BIN has a limit of 511 for its argument. Bizarre but that's the way it is!

Hi Tusharm,

Actually the last 4 bits is still treated as a single entity, that's why instead of 001 hex for positive, it's 401 hex for negative, the 4 being represented 0100. These data are actually being inputted to a Digital to Analog circuit. If the sign bit is 0, the output is positive voltage. If the sign bit is 1, the output is negative voltage. So 2s complement just won't work.

My problem is in implementing the procedure I outlined above. Sad to say, my experience with excel is very limited and for VBA is nil. I did went through the excel help regarding decimal, hexadecimal and binary; that's why I found out about DEC2HEX.

Assuming your decimal number is in A1, does this formula do what you want...

=DEC2HEX(A1+1024*(A1<0))

Ummm...so if A1 is -1023, the formula will evaluate to dec2hex(-1023+1024)!

What will work is dec2hex(abs(a1)+1024*(a1<0))

But, we still cannot get to the binary result, if that's what is required.

Assuming your decimal number is in A1, does this formula do what you want...

=DEC2HEX(A1+1024*(A1<0))

Ummm...so if A1 is -1023, the formula will evaluate to dec2hex(-1023+1024)!

What will work is dec2hex(abs(a1)+1024*(a1<0))

But, we still cannot get to the binary result, if that's what is required.
@Tushar
-----------------
Whoops! You are right, I forgot to include the ABS function call in there... thanks for catching that.

I wasn't sure that the binary representation was actually what the OP was after. He started this thread by saying...

"I had used the DEC2HEX to convert a column of decimal numbers
anywhere from 0 to 1023. It was ok and I got values from 000 to 3FF
corresponding to 0000 0000 0000 and 0011 1111 1111 in binary. My
problem now is when it is from -1 to -1023. The values I got are from
FFFFFFFFFF to FFFFFFFC01."

I got the impression from this and the other parts of his post that the binary numbers were provided for example purposes to show how to form the number that would generate the hex values he wanted.

@clea.patlecxerm
-------------------------
Following up on the first part of my response to Tushar, the formula I should have posted is this one...

=DEC2HEX(ABS(A1)+1024*(A1<0))

Sorry for any confusion.

Last edited:
Hi guys,<br><br>Thanks for all your replies.  I'm trying to peruse through all the solutions you have recommended and going through the excel help to understand what the function/s mean.  To make myself clearer,  here is the portion of the worksheet that I've been working on.  How do you insert a portion of a worksheet just like what pgc01 did in #4?<br><br> =DEC2HEX(ABS(A1)+1024*(A1<0)) works, and so is <br><br>***  To my understanding,  if A1 less than 0,  1024 (400H) will be added to the absolute value of A1.  It's like offsetting the dec2hex value by 400,  taking care of the 11th sign bit.  Works like a charm!<br><br>The final installment is to break up the hexadecimal numbers as shown below:<br><br>1.  The first byte of Hex A goes to Lo-Byte A.<br>2.  The first byte of Hex B goes to Lo-Byte B.<br>3.  The remaining nibbles of Hex A and B form Mid Byte C.  They are arranged in such a way that nibble B becomes the High nibble and nibble A becomes the Low nibble of Mid Byte C.<br><br>Best regards,<br>Clea.patlecxerm<br>
<table border="0" cellpadding="0" cellspacing="0" width="577"><colgroup><col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74">
<col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70">
<col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77">
<col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76">
<col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80">
<col style="mso-width-source:userset;mso-width-alt:4205;width:86pt" width="115">
<col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85">
</colgroup><tbody><tr style="height:15.0pt" height="20">
<td class="xl72" style="height:15.0pt;width:56pt" height="20" width="74">DEC A</td>
<td class="xl72" style="border-left:none;width:53pt" width="70">HEX A</td>
<td class="xl72" style="border-left:none;width:58pt" width="77">DEC B</td>
<td class="xl72" style="border-left:none;width:57pt" width="76">HEX B</td>
<td class="xl72" style="border-left:none;width:60pt" width="80">LO-BYTE A</td>
<td class="xl72" style="border-left:none;width:86pt" width="115">MID BYTE C<br></td>
<td class="xl72" style="border-left:none;width:64pt" width="85">LO-BYTE B</td>
</tr>
<tr style="height:15.0pt" height="20">
<td class="xl73" style="height:15.0pt;border-top:none" height="20">0</td>
<td class="xl74" style="border-top:none;border-left:none">000</td>
<td class="xl68" style="border-top:none;border-left:none">1023</td>
<td class="xl75" style="border-top:none;border-left:none">3FF</td>
<td class="xl69" style="border-top:none;border-left:none">00</td>
<td class="xl71" style="border-top:none;border-left:none">30</td>
<td class="xl70" style="border-top:none;border-left:none">FF</td>
</tr>
<tr style="height:15.0pt" height="20">
<td class="xl73" style="height:15.0pt;border-top:none" height="20">33</td>
<td class="xl74" style="border-top:none;border-left:none">021</td>
<td class="xl68" style="border-top:none;border-left:none">977</td>
<td class="xl75" style="border-top:none;border-left:none">3D1</td>
<td class="xl69" style="border-top:none;border-left:none">21</td>
<td class="xl71" style="border-top:none;border-left:none">30</td>
<td class="xl70" style="border-top:none;border-left:none">D1</td>
</tr>
<tr style="height:15.0pt" height="20">
<td class="xl73" style="height:15.0pt;border-top:none" height="20">252</td>
<td class="xl74" style="border-top:none;border-left:none">0FC</td>
<td class="xl68" style="border-top:none;border-left:none">790</td>
<td class="xl75" style="border-top:none;border-left:none">316</td>
<td class="xl69" style="border-top:none;border-left:none">FC</td>
<td class="xl71" style="border-top:none;border-left:none">30</td>
<td class="xl70" style="border-top:none;border-left:none">16</td>
</tr>
<tr style="height:15.0pt" height="20">
<td class="xl73" style="height:15.0pt;border-top:none" height="20">449</td>
<td class="xl74" style="border-top:none;border-left:none">1C1</td>
<td class="xl68" style="border-top:none;border-left:none">547</td>
<td class="xl75" style="border-top:none;border-left:none">223</td>
<td class="xl69" style="border-top:none;border-left:none">C1</td>
<td class="xl71" style="border-top:none;border-left:none">21</td>
<td class="xl70" style="border-top:none;border-left:none">23</td>
</tr>
<tr style="height:15.0pt" height="20">
<td class="xl73" style="height:15.0pt;border-top:none" height="20">789</td>
<td class="xl74" style="border-top:none;border-left:none">315</td>
<td class="xl68" style="border-top:none;border-left:none">253</td>
<td class="xl75" style="border-top:none;border-left:none">0FD</td>
<td class="xl69" style="border-top:none;border-left:none">15</td>
<td class="xl71" style="border-top:none;border-left:none">03</td>
<td class="xl70" style="border-top:none;border-left:none">FD</td>
</tr>
<tr style="height:15.0pt" height="20">
<td class="xl73" style="height:15.0pt;border-top:none" height="20">952</td>
<td class="xl74" style="border-top:none;border-left:none">3B8</td>
<td class="xl68" style="border-top:none;border-left:none">29</td>
<td class="xl75" style="border-top:none;border-left:none">01D</td>
<td class="xl69" style="border-top:none;border-left:none">B8</td>
<td class="xl71" style="border-top:none;border-left:none">03</td>
<td class="xl70" style="border-top:none;border-left:none">1D</td>
</tr>
<tr style="height:15.0pt" height="20">
<td class="xl73" style="height:15.0pt;border-top:none" height="20">1023</td>
<td class="xl74" style="border-top:none;border-left:none">3FF</td>
<td class="xl68" style="border-top:none;border-left:none">1</td>
<td class="xl75" style="border-top:none;border-left:none">001</td>
<td class="xl69" style="border-top:none;border-left:none">FF</td>
<td class="xl71" style="border-top:none;border-left:none">03</td>
<td class="xl70" style="border-top:none;border-left:none">01</td>
</tr>
<tr style="height:15.0pt" height="20">
<td class="xl73" style="height:15.0pt;border-top:none" height="20">991</td>
<td class="xl74" style="border-top:none;border-left:none">3DF</td>
<td class="xl68" style="border-top:none;border-left:none">-32</td>
<td class="xl75" style="border-top:none;border-left:none">420</td>
<td class="xl69" style="border-top:none;border-left:none">DF</td>
<td class="xl71" style="border-top:none;border-left:none">43</td>
<td class="xl70" style="border-top:none;border-left:none">20</td>
</tr>
<tr style="height:15.0pt" height="20">
<td class="xl73" style="height:15.0pt;border-top:none" height="20">864</td>
<td class="xl74" style="border-top:none;border-left:none">360</td>
<td class="xl68" style="border-top:none;border-left:none">-34</td>
<td class="xl75" style="border-top:none;border-left:none">422</td>
<td class="xl69" style="border-top:none;border-left:none">60</td>
<td class="xl71" style="border-top:none;border-left:none">43</td>
<td class="xl70" style="border-top:none;border-left:none">22</td>
</tr>
<tr style="height:15.0pt" height="20">
<td class="xl73" style="height:15.0pt;border-top:none" height="20">513</td>
<td class="xl74" style="border-top:none;border-left:none">201</td>
<td class="xl68" style="border-top:none;border-left:none">-600</td>
<td class="xl75" style="border-top:none;border-left:none">658</td>
<td class="xl69" style="border-top:none;border-left:none">01</td>
<td class="xl71" style="border-top:none;border-left:none">62</td>
<td class="xl70" style="border-top:none;border-left:none">58</td>
</tr>
<tr style="height:15.0pt" height="20">
<td class="xl73" style="height:15.0pt;border-top:none" height="20">65</td>
<td class="xl74" style="border-top:none;border-left:none">041</td>
<td class="xl68" style="border-top:none;border-left:none">-973</td>
<td class="xl75" style="border-top:none;border-left:none">7CD</td>
<td class="xl69" style="border-top:none;border-left:none">41</td>
<td class="xl71" style="border-top:none;border-left:none">70</td>
<td class="xl70" style="border-top:none;border-left:none">CD</td>
</tr>
<tr style="height:15.0pt" height="20">
<td class="xl73" style="height:15.0pt;border-top:none" height="20">1</td>
<td class="xl74" style="border-top:none;border-left:none">001</td>
<td class="xl68" style="border-top:none;border-left:none">-1023</td>
<td class="xl75" style="border-top:none;border-left:none">7FF</td>
<td class="xl69" style="border-top:none;border-left:none">01</td>
<td class="xl71" style="border-top:none;border-left:none">70</td>
<td class="xl70" style="border-top:none;border-left:none">FF</td>
</tr>
</tbody></table><br>

Hello Pgc01,<br><br>How does the nesting works?<br><br>=TEXT(SUMPRODUCT(HEX2BIN(MID(DEC2HEX(A1+(A1<0)*(1024-2*A1),3),{3,2,1},1))*{1,10000,100000000}),REPT(0,12)) <br><br><br>Thanks.<br>

Replies
3
Views
335
Replies
11
Views
9K
Replies
7
Views
13K

1,203,453
Messages
6,055,533
Members
444,794
Latest member
HSAL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back