CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
Elakkiya Lucky
Elakkiya Lucky

Reputation: 168

HEX2OCT formula in MS Excel returns incorrect result

While converting the hexadecimal value "FFFFFFFF00" into octal value using Hex2Oct of MS Excel, it should return "Error string" as per the rules mentioned here:

If number is negative, HEX2OCT ignores places and returns a 10-character octal number.

If number is negative, it cannot be less than FFE0000000, and if number is positive, it cannot be greater than 1FFFFFFF.

If number is not a valid hexadecimal number, HEX2OCT returns the #NUM! error value.

If HEX2OCT requires more than places characters, it returns the #NUM! error value.

If places is not an integer, it is truncated.

If places is nonnumeric, HEX2OCT returns the #VALUE! error value.

If places is negative, HEX2OCT returns the #NUM! error value.

But it computes and returns as "7777777400" without considering the rules/remarks mentioned in the link.

For example:

While calculating HEX2OCT,

As per Excel rule, If number is positive, it cannot be greater than 1FFFFFFF(hex)<->3777777777(oct)<->536870911(decimal).

But while calculating the HEX2OCT for FFFFFFFF00(hex) <-> 7777777400(oct) <-> 1099511627520(decimal).

Here the hex value FFFFFFFF00 is greater than 1FFFFFFF, but MS Excel does not return the error string instead it returns the converted octal value.

Can anyone explain why?

Upvotes: 3

Views: 123

Answers (2)

JNevill
JNevill

Reputation: 50064

FFFFFFFF00 is actually well within the range of hex2oct because it is a negative number.

According to that documentation the largest negative number it can handle is FFE0000000 which when converted to decimal is -536870912. Converting your "big" hex over to decimal yields -256.

The reason the value of FFFFFFFF00 looks so big is because it's a negative number. The first bit is set to 1 (when converted to binary) which signifies that the number is negative. Negatives are computed in binary using two's complement which is found by flipping each bit and then adding 1 to the number.

Undoing the two's complement:

For your big number, the binary representation is:

 1111111111111111111111111111111100000000

Subtracting 1:

 1111111111111111111111111111111011111111

Flipping all the bits:

 0000000000000000000000000000000100000000

Which is 256

So.. basically if the hex looks big, but the first bit is 1 then it's actually a small negative and well within your range of allowable values.

Lastly, when you hex2oct you don't get a negative sign for these because we are still not in decimal notation. The first bit of your octal is still a 1 (when converted to binary) since it's still the same number, just represented in a different counting system.

Upvotes: 2

AakashM
AakashM

Reputation: 63338

The clue lies earlier in the documentation page you quote:

The HEX2OCT function syntax has the following arguments:

Number Required. The hexadecimal number you want to convert. Number cannot contain more than 10 characters. The most significant bit of number is the sign bit. The remaining 39 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

The hex value FFFFFFFF00 corresponds the binary value

1111 1111 1111 1111 1111 1111 1111 1111 0000 0000

and as the documentation says, "the most significant bit is the sign bit ... two's complement notation". So this value represents a negative number. By the rules of two's complement, it actually represents -256. And this is fine, because it is not "less than FFE0000000", as FFE0000000 is -2097152.

If you actually want to treat FFFFFFFF00 as an unsigned quantity, and get the octal representation of decimal 1099511627520, you'll need to use another method.

Upvotes: 1

Related Questions