INT function returns unexpected results

In trying to answer another user's question, I created a prototype workbook using this formula

=INT((C1-INT(C1))*10)

where C1 contains an arbitrary number. (See attachment.) This formula is intended to return the first digit after the decimal point as an integer. And it works, except when C1 contains numbers from 8.2 to 31.2 (inclusive) with only a 2 after the decimal point.

I broke the formula down into multiple formula steps. The error occurs in the final step when applying INT to the integer 2, returning 1. But this only happens in the cases just described. It seems that INT does not see every 2 as the same number.

This may be some obscure binary precision issue, but if you take 0.2, multiply it by 10, then Excel shows it as an integer 2, I would think you are dealing with an exact representation of the integer 2. And the problem only occurs in some cases with a decimal portion of 0.2, but not others.

Anybody ever see anything like this?

Floating-point precision problem.

8.2 - 8 = 1.99999.... = 3FC9999999999980

0.2 = 3FC999999999999A

You can round after the subtraction, or extract the result from the formatted string.

Originally Posted by shg

Floating-point precision problem.

8.2 - 8 = 1.99999.... = 3FC9999999999980

0.2 = 3FC999999999999A

You can round after the subtraction, or extract the result from the formatted string.

I suspected something like that. I actually used to teach stuff like this (model numbers and safe numbers) but that was 20 years ago and haven't thought about it for a while.

This question is more an exploration of internals than an immediate pragmatic issue, so I hope my persistence isn't annoying. I understand how to work around this in specific cases, but it gives me pause to think that I have to carefully consider every future use of INT.

It does raise one question:

Why does it work correctly for 2.2, 3.2, 4.2, 5.2, 6.2, 7.2 then fail at 8.2? And then start working again at 32.2, and apparently for all higher numbers?

Because 32.2 - 32 = 0.200000000000003, which errs beneficially for this.

Originally Posted by darkyam

Another option is to just use text functions, such as
=MID(C1,FIND(".",C1)+1,1)+0

I advised against that because it returns #VALUE! if the number is an integer....

Originally Posted by shg

8.2 - 8 = 1.99999....

Actually I read in a math book recently that 1.99999... and 2 are considered to be the same number by mathematicians because by definition if there are no values between two represented numbers on a number line, then the two numbers are equivalent. Yes, I know that Excel only takes it out to 15 digits, just trying to add some trivia.

Originally Posted by 6SJ

Originally Posted by darkyam

Another option is to just use text functions, such as
=MID(C1,FIND(".",C1)+1,1)+0

I advised against that because it returns #VALUE! if the number is an integer

So wrap a TEXT function around C1.

In this case, processing the result of the conversion to decimal is exactly what you want.

just for info
put 1.2 in a1
2.2 in a2
in b1 put a1-row()
and in b2 =a2-row()
select a1:b2 drag down formatted as number as many places as you want you'll see where it changes