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

 

[alanharper]

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)

Thanks Rick. That worked as well. Now I need to generalize it so that it works for all signed hex numbers. This formula works for negative hex numbers but not for positive hex numbers. I think I need to determine if the first digit is 0-7. I need GREP but I don't know how, or it, I can do GREP expressions inside an Excel formula.

 

[Rick Rothstein]

Thanks Rick. That worked as well. Now I need to generalize it so that it works for all signed hex numbers. This formula works for negative hex numbers but not for positive hex numbers. I think I need to determine if the first digit is 0-7. I need GREP but I don't know how, or it, I can do GREP expressions inside an Excel formula.

I think our messages crossed in the ether... see Message #10 (refresh your view if necessary).

 

[alanharper]

Give this formula a try...

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

This worked beautifully. It has been so long since I knew Excel this well, but it is now coming back. Thanks again for the help, much appreciated.