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

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

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

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

Cheers
Chris

"Mike H" wrote:

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

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

"David Biddulph" wrote:

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




.

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

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

"Mike H" wrote in message
...
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

"David Biddulph" wrote:

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




.

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

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" wrote:

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




.

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. :-)
--
David Biddulph

"Mike H" wrote in message
...
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" wrote:

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




.

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" wrote:

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. :-)
--
David Biddulph

"Mike H" wrote in message
...
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" wrote:

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




.



.

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.
--
David Biddulph

"Mike H" wrote in message
...
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" wrote:

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. :-)
--
David Biddulph

"Mike H" wrote in message
...
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" wrote:

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




.



.