Decimal to Hexadecimal to Binary with a twist

[clea.patlecxerm]

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.

 

[clea.patlecxerm]

Hello Tusharm,<br><br>I tried this and it works.  However,  at this time,  I can understand the several nesting and multiple functions involved.  Can you explain further.<br><br>=IF(G14<0,"01","00")&DEC2BIN(INT(ABS(G14)/512),1)&DEC2BIN(MOD(ABS(G14),512),9)<br><br>However,  I didn't see the pattern of 4 binary digits separated by a space.<br><br>Thank you.<br>

 

[tusharm]

The original question seemed like it might be a HW assignment. This extension makes it seem even more so.

Check out the Excel functions MID, RIGHT, and LEFT as well as the concatenation operator &.

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>

 

[clea.patlecxerm]

Hi Tusharm,

=RIGHT(D2,2) got FF for Lo-Byte B.
=RIGHT(B2,2) got 00 for Lo-Byte A.
=LEFT(D2,1)&LEFT(B2,1) got 30 for Mid Byte C.

Perfect.

One last thing to do. How do you save the values of columns Lo-Byte A, Lo-Byte B and Mid Byte C in their own binary file(one column, one file)? Since the contents (in a single column) are already in Hexadecimal numbers, do I need to convert them to binary?

Thanks.

 

[tusharm]

A great way to understand (and learn) about Excel formulas is to use its Evaluate Formula capability.

Select a cell with a formula, then click Formulas tab | Formula Auditing group | Evaluate Formula button.

As far as creating a pattern of 4 digits separated by a space goes, see the latter part of my post. You have to convert the result in a number (that's that the -- part does) and then apply the custom format (Home tab | Number gropu | Format dropdown | More Number Formats... button

Hello Tusharm,<br><br>I tried this and it works.  However,  at this time,  I can understand the several nesting and multiple functions involved.  Can you explain further.<br><br>=IF(G14<0,"01","00")&DEC2BIN(INT(ABS(G14)/512),1)&DEC2BIN(MOD(ABS(G14),512),9)<br><br>However,  I didn't see the pattern of 4 binary digits separated by a space.<br><br>Thank you.<br>