CODEWITHSUNDEEP
excelexcel-formulaunsigned-integersigned-integer
Yangorang
Yangorang

Reputation: 9

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/

Upvotes: 0

Views: 4968

Answers (2)

Old-Coder
Old-Coder

Reputation: 41

=IF(BITAND(A1,32768)=32768,A1-65536,A1)

Examples:

  1. If cell A1 = 64973 (hex value FDCD)then above returns -563
  2. 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

Upvotes: 2

mark fitzpatrick
mark fitzpatrick

Reputation: 3322

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

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

Upvotes: 1

Related Questions