Decimal to Hexadecimal to Binary with a twist

C

clea.patlecxerm

New Member
Joined
Jun 5, 2012
Messages
6
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
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.
clea.patlecxerm said:
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.
Click to expand...
 
Upvote 0
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.
 
Upvote 0
Hi

Try:

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


<TABLE style="BORDER-BOTTOM-COLOR: #cccccc; BORDER-RIGHT-WIDTH: 2px; BORDER-TOP-COLOR: #cccccc; BORDER-COLLAPSE: collapse; FONT-FAMILY: Arial,Arial; BACKGROUND: #fff; BORDER-TOP-WIDTH: 2px; BORDER-BOTTOM-WIDTH: 2px; BORDER-RIGHT-COLOR: #cccccc; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #cccccc; BORDER-LEFT-WIDTH: 2px" border=1 cellPadding=1><TBODY><TR><TH style="BORDER-BOTTOM-COLOR: #888888; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px"> </TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px">A</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px">B</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>C</TH></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">0</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">000000000000</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">2</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">000000000001</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">3</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">-1</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">010000000001</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1023</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">001111111111</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">5</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">-1023</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">011111111111</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">6</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="PADDING-LEFT: 1em; BACKGROUND: #9cf" colSpan=4>[Book1]Sheet1</TD></TR></TBODY></TABLE>
 
Upvote 0
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!

clea.patlecxerm said:
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.
Click to expand...
 
Upvote 0
Assuming your decimal number is in A1, does this formula do what you want...

=DEC2HEX(A1+1024*(A1<0))
 
Upvote 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.

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

=DEC2HEX(A1+1024*(A1<0))
Click to expand...
 
Upvote 0
tusharm said:
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.
Click to expand...
@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:
Upvote 0
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>
 
Upvote 0
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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Break single hexadecimal number up into its 4 binary components
Replies
3
Views
402
Z51
Z
S
  • Question
Very long Binary Number but Excel treats it as Decimal one
2
Replies
11
Views
10K
pgc01
P
I
  • Question
manipulation of binary numbers
Replies
7
Views
13K
RoryA
RoryA

Forum statistics

Threads
1,216,057
Messages
6,128,523
Members
449,456
Latest member
SammMcCandless

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top