INT issue

[bsmith81]

So, I'm converting bond prices from a price feed which takes the form ###.@@%
where # = normal integers
@@= are @@/32
and % = 1/8 of 32nds

Anyway, to my question:

Say i have 103.215 in cell A1 - that's 103 + 21/32 + 5/256

So, to convert that, I have the formula in the adjacent cell:
=INT((A1-INT(JA1))*100)

I'm taking the approach of breaking out the digits behind the decimal into @@ and % and then converting and then adding them together to get the decimal form. That formula gives me the whole number 21, which i can then convert to 32nds by dividing. Now let's say I have the number 103.21 in Cell A1 - the output of that formula is 20!!!

The error only happens when there are two digits ONLY behind the decimal.

How does the INT function work that it makes this silly error? I've also tried TRUNC but that gives me the same output.

[NBVC]

The calculation within the parentheses calculates to 20.09999999994, you just need to round it up.

Try incorporating the Round() function:

=INT(ROUND((A1-INT(A1))*100,1))

[daddylonglegs]

This is the result of the way excel calculates using "floating point arithmetic", see here.

=(A1-INT(A1))*100 probably gives a result something like 20.9999999 then when you take the integer part with INT you get 20 not 21.

Try using

=INT(MOD(A1*100,100))

[MickeyGreen]

This gets it done. If need be, the steps can be combined into a single formula.

[bsmith81]

Interesting - Always good to know a bit about how excel calculates stuff.

Good solutions - thanks for your help!