If function confused with 0.1

[Chris B]

Hi,

I have set up the following If function to determine if the answer to a
simple subtraction of two decimal numbers is 0.1.

=IF(C1=0.1,"yes","no")

The calculation is merely C1=A1-B1

However, for some calculations it is giving the answer "no" when the answer
is clearly 0.1!

1.7-1.6 gives a no response as does 1.4-1.3. Interestingly, from 4.1-4 all
answers are no!

Hope someone can help
Cheers

 

[Mike H]

Chris,

It's all to do with the way Excel does arithmetic. If you do you sum 1.7-1.6
and expand the formula cell to 9 decimal places you'll see the answer comes
out as 0.0999999 and while that may seem odd it entirely meets the IEEE
standard for floating point arithmetic.

Have a look here for a full explanation

http://support.microsoft.com/default.aspx/kb/78113

Now to your proble, try this

=IF(ROUND(C1,1)=0.1,"yes","no")

Mike

 

[David Biddulph]

Which version of Excel are you using, Mike? With Excel 2003, 1.7-1.6 gives
me 0.100000000000000 if I format to 15 decimal places, so I am confused as
to how you are seeing the figure you quote for 9 decimal places (or even 7
as you have shown). I wonder whether you (and the OP) are getting confused
by starting with numbers that aren't 1.7 and 1.6 (or the closest fixed point
binary representations thereof). Are they the result of calculations? What
do you see if you expand them to 15 decimal places? And what if you retype
1.7 and 1.6?

It is, of course, true that there is no exact binary representation of 1.7,
1.6, or 0.1.

 

[Chris B]

Thanks Mike, I guessed it may be down to a rounding error

Cheers
Chris

 

[Mike H]

David,

I originally tested my answer to the OP in E2007

A1=1.7
B1=1.6
=a1-b1 gives 0.09999999999999990 when set to lots of decimal places

Re-tested in e2003, same cell setup and I get what I expected
0.09999999999999990000

Mike

 

[Bernard Liengme]

David,
I get 0.099999999999... in XL2003 with =1.7-1.6
Not surprising since 0.1 is one of the decimal numbers that has no exact
representation in the IEEE convention
best wishes

 

[David Biddulph]

Yes, you need to format to 16 decimal places or more to see it that way.
You don't see it if formatted to 15 places, or to 9 or 7 as your previous
post implied, but the fact that there is no exact fixed point binary
representation does indeed mean that =1.7-1.6=0.1 returns FALSE.

 

[David Biddulph]

Not surprising, but of course the problem for the OP is that if you show it
to 16 or more decimal places 0.1 *does* appear as 0.1000000000000000,
whereas 1.7-1.6 appears as 0.09999999999999990.
I had pointed out in my message that "there is no exact binary
representation of 1.7, 1.6, or 0.1."
It was Mike's 9 or 7 decimal place example that had me worried, but I gather
that this was just an abbreviation of what he'd actually seen.

 

[Mike H]

david,

To remove (or maybe add) confusion if I format all cells to 16 decimal
places I see in both E2003 & E2007


a1 1.7000000000000000
b1 1.6000000000000000
C1 0.0999999999999999

So A1 & b1 are truly 1.7 & 1.6.

Mike

 

[David Biddulph]

To see whether A1 and B1 are "truly" 1.7 and 1.6, try to work out what the
*exact* binary representation of 1.7 would be. Similarly for 1.6.
Come back to us when you've worked it out.

 

[Mike H]

Come back to us when you've worked it out.

I'm fully aware that there is no exact representation of 1.6 so won't bother
trying to work it out

 

[David Biddulph]

Fine. In which case you understand why you don't get an exact
representation for the difference either. In some case you may be lucky and
get the number that you might have hoped for, but in others you don't, so
rather than looking for equality you are better setting limits, or using the
ROUND function.