Results 1 to 4 of 4

floating point decimal to hex converion?

This is a discussion on floating point decimal to hex converion? within the Excel Questions forums, part of the Question Forums category; Is there any macro out there that does a proper floating point decimal number to hex converion? i.e. convert a ...

  1. #1
    Board Regular
    Join Date
    Jun 2006
    Posts
    51

    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,201

    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
    51

    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,201

    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

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