Signed hex numbers

[alanharper]

I teach computer forensics classes and we have the need for a formula that will determine if any length hex number (typically 2,3, or 4 bytes) is negative and, if so, convert that number to its negative decimal equivalent. I have been able to do that with the following formula (cell E5 contains the negative hex number F90825):

=-(HEX2DEC("FFFFFF")-HEX2DEC(E5)+1)

If E5 contains F90825 then this formula will return the correct decimal value of -456,667. This formula works because it calculates the 15's complement of the number, adds 1 and then negates the result. If the formula is used on a hex value that is less than three bytes, it fails to return the correct number. For example, if E5 contained the value 9B06, the formula would return the value -16,737,530. However, if I adjust the number of F's to match the number of bytes in the hex number, the formula works. That seems to be the key to making this formula work.

I now need a modified formula or process to look at the hex number, determine the number of bytes (I think LEN can do that) and then adjust the formula to include the same number F's as there are hex digits in the number. I know I need an IF statement and some way to create a formula on the fly. A simple CASE statement from my programming days would work, but I did not see anything like that in Excel.

It also interesting to note that if these calculations are performed on the Windows calculator in Programming mode, you can simply enter the number and NOT it. This immediately calculates the 15's complement. The NOT function in Excel is a Boolean operator so it doesn't work. I think perhaps MOD would work in Excel, but I am not sure how to create the formula.

I hope that I have explained the problem well enough that someone out there can help.

Regards ... Alan Harper

 

[martindwilson]

=-(HEX2DEC(REPT("F",LEN(E5)))-HEX2DEC(E5)+1) maybe

 

[Rick Rothstein]

I don't think you can just look at a Hex number and know if it is positive or negative... I think you need to know the number of bits for the range of numbers you are looking at. The way to determine if an N-bit signed number is negative or positive is by looking at the number in binary (zero-filled to N bits if needed) and seeing if its leading bit is a 0 or 1... if it is 0, the number is positive and, if it is 1, the number is negative. Just looking at Hex number, in isolation, would mean nothing sign-wise as far as I know. By the way, if I remember correctly, for a signed Hex number, filled out to the proper bit-level, if the lead Hex number is between 0 an 7, the number is positive and, if it is between 8 and F, the number is negative.

 

[alanharper]

Thanks for the reply. I know that the number I need to convert is either positive or negative based on the leading hex digit as you stated as these numbers come from a $80 attribute in an MFT FILE record. I know the numbers are signed. What I need is a formula to do what I asked.

 

[alanharper]

Sounds promising. I'll give it a try.

 

[alanharper]

That worked. Thanks so much for the quick response.

 

[shg]

There's no way to know unless you also know the bit length the format (unless by understanding any leading zeros are included, and the format is known to be a multiple of four bits) and the encoding. In VBA, Currency, Long, and Integer are all signed twos-complement; Byte is not. In other languages, there are signed and unsigned integers of varying lengths. The mantissas of Singles and Doubles are stored as sign and magnitude, and their exponents as unsigned with offsets.

 

[alanharper]

The formula that martindwilson suggested works for the negative hex numbers. Now I need to generalize it so it works with any signed hex number. I think an IF statement is needed but I don't know how to tell if the left most digit of the hex number is 8-F.

 

[Rick Rothstein]

=-(HEX2DEC(REPT("F",LEN(E5)))-HEX2DEC(E5)+1) maybe

That worked. Thanks so much for the quick response.

Okay, if that is what you want, here is a more compact formula (two less function calls, so it should be more efficient) that you can use..

=HEX2DEC(E5)-16^LEN(E5)

 

[Rick Rothstein]

The formula that martindwilson suggested works for the negative hex numbers. Now I need to generalize it so it works with any signed hex number. I think an IF statement is needed but I don't know how to tell if the left most digit of the hex number is 8-F.

Give this formula a try...

=HEX2DEC(E5)-IF(ISERR(FIND(LEFT(E5),"01234567")),16^LEN(E5),0)