Q:

Hex values in one word in Excel

How do I make Excel 2016 convert hex value in one word (two bytes) into a decimal (0xffff must convert to -1 and not to 65535)?
Thanks

A:

Assuming you have your 16-bit (2-byte) hex value (e.g. "FFFF") in cell A1, you could do this:
=BITXOR(HEX2DEC(A1),2^15)-2^15

This will:

Convert the hex value to unsigned decimal (with the intended negative range of numbers starting at 2^15, i.e. above the intended non-negative range).
Invert the most significant bit (bit 15) in order to swap the intended non-negative and negative ranges (both ranges are still non-negative at this point, but now the intended negative range is below the intended non-negative range).
Shift both ranges down so the intended negative range reaches its final destination below zero, and the intended non-negative range reaches its final destination at zero and above.

Examples: "0" becomes 0, "7FFF" becomes 32767, "8000" becomes -32768, and "FFFF" becomes -1.
Note: If the input value contains garbage (e.g. "0x") before or after the actual hex value, then this garbage must be removed (e.g. by doing MID(A1,3,4)) before calling HEX2DEC.

