# 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. ## 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. 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. 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. 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

#### Posting Permissions

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