'Converting unsigned to signed integers (Using Excel formulas or Power Query)
I have a list of unsigned 32-bit unsigned integers that actually are supposed to represent 32-bit signed integers. For example 62714 = binary 1111010011111010 = signed -2822 when interpreted as two's complement Basically I am trying find some formula to get from 62714 to -2822
The solution listed here does not seem to work: https://ocroquette.wordpress.com/2015/10/26/converting-unsigned-to-signed-integers-using-powershell-or-excel/
Solution 1:[1]
It appears that you are using 16 bit signed integers, so you could do:
=MOD(A1+2^15,2^15)-2^15
Solution 2:[2]
=IF(BITAND(A1,32768)=32768,A1-65536,A1)
Examples:
- If cell
A1 = 64973 (hex value FDCD)then above returns -563 - If cell
A1 = 1000 (hex value 03E8)then above returns 1000
Useful functions to convert text hex strings to decimal and back: HEX2DEC and DEC2HEX
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | mark fitzpatrick |
| Solution 2 | procrastinator |