excelfloating-pointieee-754floating-point-conversion

Convert number From Excel cell to IEEE 754 Hex format

Recently I have been wrestling with Excel due to the 15 significant digits display limit for a number. I have been looking for a way to display the IEEE 754 format of a value contained in a Excel cell (since they are documented to work that way).

I'd prefer not to rely on VBA for this project (although I might get tempted if a memcpy-like solution is possible).

See my answer below for my current implementation. Any input or alternative is appreciated. I choose to believe that I missed an easier, well-tested solution.

Solution

  • The following sequence allow me to convert a number to its IEEE 754 hexadecimal representation using Excel formulas. I did not try to handle any exceptions besides 0. From Cell A1 to G1:

    A few of my result:

    EDIT: Follow-up to chux comments.

    We can see that the following value give a wrong result due to a rounding error:

    Under this scenario, the value given at the step D1 is negative. If I use this information to update my exponent, my results appear to be consistent:

    Here is the updated B1 formula:

    EDIT2: above steps in a single function using LET (Available in Microsoft 365)

    =LET(num,A1,
exp,INT(LOG(ABS(num),2)),
exponent,IF((ABS(num)/(2^exp)-1)*(2^52)<0,exp-1,exp),
mantissa,(ABS(num)/(2^exponent)-1)*(2^52),
part_a,DEC2HEX(1023+exponent+IF(num<0,2^11,0),3),
part_b,CONCATENATE(DEC2HEX(mantissa/2^32,5),DEC2HEX(MOD(mantissa,2^32),8)),
"0x"&IF(num=0,0,part_a&part_b))

    EDIT3: as the point was already raised twice, I am adding a "most likely correct" answer for 32-bit float format.

    =LET(num,A1,
exp,INT(LOG(ABS(num),2)),
exponent,IF((ABS(num)/(2^exp)-1)*(2^23)<0,exp-1,exp),
base_mantissa,(ABS(num)/(2^exponent)-1)*(2^23),
round_nearest_tie_even,IF(base_mantissa-INT(base_mantissa)=0.5,EVEN(base_mantissa),ROUND(base_mantissa,0)),
"0x"&IF(num=0,0,DEC2HEX((127+exponent+IF(num<0,2^8,0))*(2^23)+round_nearest_tie_even,8)))

    This was less straightforward than expected: