HEX2OCT formula in MS Excel returns wrong result
When converting the hexadecimal value "FFFFFFFF00" to octal using Hex2Oct MS Excel, it should return the string "Error" according to the specified rules here :
If the number is negative, HEX2OCT ignores spaces and returns an octal octal number.
If the number is negative, it cannot be less than FFE0000000, and if the number is positive, it cannot exceed 1FFFFFFF.
If number is not a valid hexadecimal number, HEX2OCT returns #NUM! error value.
If HEX2OCT requires more than character spaces, it returns #NUM! error value.
If the spaces are not integers, they are truncated.
If places are not numeric, HEX2OCT returns #VALUE! error value.
If the spaces are negative, HEX2OCT returns #NUM! error value.
But it calculates and returns as "7777777400" disregarding the rules / remarks mentioned in the link.
For example:
When calculating HEX2OCT,
As per Excel rule, if a number is positive, it cannot exceed 1FFFFFFF (hex) ↔ 3777777777 (oct) ↔ 536870911 (decimal).
But when calculating HEX2OCT for FFFFFFFF00 (hex) ↔ 7777777400 (oct) ↔ 1099511627520 (decimal).
Here the hexadecimal value FFFFFFFF00 is greater than 1FFFFFFF, but MS Excel does not return the error string, instead it returns the converted octal value.
Can someone explain why?
source to share
FFFFFFFF00
is indeed within the range hex2oct
because it is a negative number.
According to this documentation, the largest negative number that can handle is FFE0000000
that when converted to decimal -536870912
. Converting your "large" hexadecimal value to decimal gives -256
.
The reason the value FFFFFFFF00
looks so large is because it is a negative number. The first bit is set to 1 (when converted to binary), which means the number is negative. Negative values are calculated in binary using two's complement , which is found by translating each bit and then adding 1 to the number.
Cancellation of two additions:
For your large number, the binary representation is:
1111111111111111111111111111111100000000
Subtraction 1
:
1111111111111111111111111111111011111111
Flip all the bits:
0000000000000000000000000000000100000000
What is 256
So, in principle, if the hex size looks large, but the first bit 1
, then it is actually a small negative and well within the range of valid values.
Finally, when you do hex2oct
, you don't get a negative sign for them, because we are still not in decimal notation. The first bit of your octal value is still equal 1
(when converted to binary), since it's still the same number just represented in a different counting system.
source to share
The key lies earlier on the documentation page that you point to:
The function syntax
HEX2OCT
has the following arguments:Number Required. The hexadecimal number that you want to convert. The number cannot contain more than 10 characters. The most significant bit of a number is the sign bit. The remaining 39 bits are the bit size. Negative numbers are represented using two-digit notation.
Hexadecimal value FFFFFFFF00
corresponds to 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 complement notations." Therefore, this value is a negative number. By the rules of two additions, he actually represents -256
. And this is great, because it is not "less FFE0000000
", but FFE0000000
- -2097152
.
If you really want to treat it FFFFFFFF00
as an unsigned value and get the octal representation of the decimal 1099511627520
, you will need to use a different method.
source to share