floating point decimal to hex converion?

ILv2Xlr8

Board Regular
Joined
Jun 12, 2006
Messages
60
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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top