Search code examples
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:

    • A1: 0.123456

    • B1: =INT(LOG(ABS(A1),2)) Exponent

    • C1: =ABS(A1)/(2^B1)Mantissa

    • D1: =(C1-1)*(2^52) Convert mantissa to decimal

    • E1: =DEC2HEX(1023+B1+IF(A1<0,2^11,0),3) Convert sign & exponent to hex

    • F1: =CONCATENATE(DEC2HEX(D1/2^32,5),DEC2HEX(MOD(D1,2^32),8)) Convert decimal to hex.

    • G1: ="0x"&IF(A1=0,0,E1&F1)

    A few of my result:

    • 22222.0948199999 > 0x40D5B3861187E7A5
    • =1.35632902954101*2^14 > 0x40D5B3861187E7A7
    • 22222.09482 > 0x40D5B3861187E7C0
    • 0.000123456 > 0x3F202E7EF70994DD
    • 1E+307 > 0x7FAC7B1F3CAC7433
    • -35.3 > 0xC041A66666666666
    • 1 > 0x3FF0000000000000

    EDIT: Follow-up to chux comments.

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

    • =255+0.9999999999999 > 0x40700000FFFFFFFE

    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:

    • =255+0.9999999999999 > 0x406FFFFFFFFFFFFC

    Here is the updated B1 formula:

    • B1: =IF((ABS(A1)/(2^INT(LOG(ABS(A1),2)))-1)*(2^52)<0,INT(LOG(ABS(A1),2))-1,INT(LOG(ABS(A1),2)))

    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:

    • My 64-bit answer relies on a trick to work with DEC2HEX 40-bit limit. It is unecessary (and does not work) with 32-bit.
    • The mantissa had to be rounded, I had to check the documentation against Excel's ROUND. The round part of my equation has not been tested thoroughly: test cases exists but I don't know how much time it would take me to find them.