Convert number From Excel cell to IEEE 754 Hex format

user_11979706 0
Viewed 6002

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.

floating-point-conversion ieee-754 floating-point excel
1 Answers

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)))
user_119797060
Related