Converting unsigned to signed integers (Using Excel formulas or Power Query)

Question

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/

score 2 · Answer 1 · answered Feb 12 '22 at 18:47

2

It appears that you are using 16 bit signed integers, so you could do:

=MOD(A1+2^15,2^15)-2^15

answered Feb 12 '22 at 18:47

mark fitzpatrick

3,162
2
11
23

score 1 · Answer 2 · edited Apr 22 '22 at 05:52

1

=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

edited Apr 22 '22 at 05:52

Procrastinator

2,526
30
27
36

answered Apr 16 '22 at 00:23

user17628299

11
1

Converting unsigned to signed integers (Using Excel formulas or Power Query)

2 Answers2