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.
--
David Biddulph
"Bernard Liengme" wrote in message
...
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
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP
"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph
"Mike H" wrote in message
...
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
"Chris B" wrote:
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