2

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.

AugustinLopez
  • 348
  • 1
  • 3
  • 13

2 Answers2

3

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))
AugustinLopez
  • 348
  • 1
  • 3
  • 13
  • 1
    This is how a self-answer should be done. Nice work – Mad Physicist Aug 02 '20 at 17:32
  • 1
    For me, fails for values near 1 like `0.999999999999999`. Can you generate `0x3FEFFFFFFFFFFFFF`? – chux - Reinstate Monica Aug 02 '20 at 20:46
  • @chux-ReinstateMonica I managed to generate `0x3FEFFFFFFFFFFFFF` with `=0.0099999999999999+0.99` – AugustinLopez Aug 02 '20 at 21:11
  • 2
    With values near a power of 2, I see `INT(LOG(ABS(A1);2))` having insufficient precision. Between two powers of 2, there are 2^52 different `double`, yet the reported log2 of `A1` will be a rounded result. Say `128 <= A1 < 256.0`, the log is [7.0 ... 8.0), there are not 2^52 different `double` and `A1` values near 256 will round to 8. Thus leading to the wrong hex format. Perhaps with a 2-stage determination of the _Exponent_, a rough and then a refined one. Hmmmm. – chux - Reinstate Monica Aug 02 '20 at 21:28
0

Please use following expression to convert into 32 bit hex in little endian format. Put float value in cell A1 or change the below expression

=( (RIGHT("00"&BIN2HEX((IF(IF(A1<0,-1,1)=1,0,1))&LEFT((RIGHT("00000000"&DEC2BIN(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2)))+127),8)),7)),2)) & (RIGHT("00"&BIN2HEX(RIGHT((RIGHT("00000000"&DEC2BIN(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2)))+127),8)),1)&(RIGHT("0000000"&DEC2BIN(INT(INT((IF(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2)))+127=0,ROUND((A1/2^(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2))))/(IF(A1<0,-1,1)))4194304,0),ROUND(((A1/2^(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2))))/(IF(A1<0,-1,1)))-1)(8388608),0)))/256)/256)),7))),2)) & (RIGHT("00"&BIN2HEX(RIGHT("00000000"&DEC2BIN(((INT((IF(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2)))+127=0,ROUND((A1/2^(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2))))/(IF(A1<0,-1,1)))4194304,0),ROUND(((A1/2^(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2))))/(IF(A1<0,-1,1)))-1)(8388608),0)))/256)/256)-INT(INT((IF(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2)))+127=0,ROUND((A1/2^(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2))))/(IF(A1<0,-1,1)))4194304,0),ROUND(((A1/2^(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2))))/(IF(A1<0,-1,1)))-1)(8388608),0)))/256)/256))*256),8)),2)) & (RIGHT("00"&BIN2HEX(RIGHT("00000000"&DEC2BIN((((IF(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2)))+127=0,ROUND((A1/2^(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2))))/(IF(A1<0,-1,1)))4194304,0),ROUND(((A1/2^(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2))))/(IF(A1<0,-1,1)))-1)(8388608),0)))/256)-(INT((IF(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2)))+127=0,ROUND((A1/2^(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2))))/(IF(A1<0,-1,1)))4194304,0),ROUND(((A1/2^(IF(ABS(A1)<1.1754943E-38,-127,INT(LOG(ABS(A1))/LOG(2))))/(IF(A1<0,-1,1)))-1)(8388608),0)))/256)))*256),8)),2)) )

Tested and working

user187144
  • 11
  • 1
  • It is now possible with Microsoft 365 to transform my original answer into a single function using LET without compromising readibility too much. As for your answer, it appears to be shown incorrectly, most likely because `*` is interpreted as text format. – AugustinLopez May 13 '23 at 06:45