floating point decimal to hex converion?

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: floating point decimal to hex converion?

  1. #1
    Board Regular
    Join Date
    Jun 2006
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default floating point decimal to hex converion?

     
    Is there any macro out there that does a proper floating point decimal number to hex converion?

    i.e. convert a floating point number like -0.0168 to BC89A027

    The builtin excel dec2hex function is limited to positve numbers <65535?

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi ILv2Xlr8,

    I'm not sure exactly what you want because

    1. DEC2HEX not only doesn't handle negative numbers, it also doesn't know about floating point.

    2. In your example -0.0168 may translate to BC89A027 as a 4-byte floating point number, but Excel numeric cell values are stored as 8-byte IEEE floating point numbers (the same as the VBA Double data type).

    If you want to convert from decimal to hex, including the fractional part (example -0.0168 decimal = -0.044D013A hex) I have a user-defined function I can provide you to to this (let me know).

    But if you really want the internal floating point bit string represented in hex, this is a bit trickier. The only solution I know involves the use of a custom DLL function.

    Damon

  3. #3
    Board Regular
    Join Date
    Jun 2006
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about a decimal to binary conversion for 32 bit float?
    i.e. -0.0168 to 101111001100010011010000000100111

    Not sure how a DLL would help unless I could edit it in VB to get what I need.

  4. #4
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi again ILv2Xlr8,

    It is just as difficult to do the binary conversion as the hex--and of course it is trivial to convert back and forth between hex and binary. The work is really in following all the IEEE-754 (the floating point standard) rules for handling of the mantissa, exponent bias, and sign bit in order to determine the complete bit pattern. A useful source of information about the rules and an online converter that shows the mantissa, exponent and sign can be found at

    http://babbage.cs.qc.edu/courses/cs341/IEEE-754.html

    And I just want to point out again that while VBA has the capability of doing 32-bit (4-Byte) floating point arithmetic because of its Single data type, Excel has no such data type. Excel performs all numerical calculations in 64-bit (8-Byte) floating point arithmetic. So if you type -0.0168 into an Excel cell, the underlying hex bit sequence will not be BC89A027 (4-Bytes) but will instead be BF913404EA4A8C1 (8-Bytes).

    The reason why I suggested a DLL is that VBA strong typing prevents directly accessing the underlying bit sequences of Double and Single data types--at least I haven't found a way to do it. But other programming languages can do this easily. The last time I had to do it I wrote a FORTRAN function and called this function from VBA via a Define Function interface. If you are interested in it I will see if I can find the DLL and email it to you. Of course, as I stated up front you can follow the IEEE-754 rules and do it in VBA. If you decide to do this I would be very interested in getting a copy of it from you.

    Keep Excelling.

    Damon

    PS. ILv2Xlr82

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com