Hi,

Please help in understanding this:

Cells d10,d11,d12 have the value 10.7 in them
Cell e10 has the value .7 directly input
Cell e11 has the value .7 derived by the formula =d11-int(d11)
Cell e12 has the value .7 dervied by the formula = e11-trunc(e11)
When values in cells e10,e11,e12 are tested for =.7, cell f10 shows
true, f11 and f12 show false. Why?

Thanks in Advance for the Help.

Raj

Hi,

If you format E10, E11 & E12 to a number with lots of decimal places you'll
finf that E10 is 0.7 but E11 & E12 are actually 0.6999999999999999 so hence
the evaluation as false when tested for =0.7.

The reason for this is in the way Excel does arithmetic and there being no
precise binary equivalent of 0.7. For a full explanation have a look here

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

Mike

"Raj" wrote:

Hi,

Please help in understanding this:

Cells d10,d11,d12 have the value 10.7 in them
Cell e10 has the value .7 directly input
Cell e11 has the value .7 derived by the formula =d11-int(d11)
Cell e12 has the value .7 dervied by the formula = e11-trunc(e11)
When values in cells e10,e11,e12 are tested for =.7, cell f10 shows
true, f11 and f12 show false. Why?

Thanks in Advance for the Help.

Raj

I should have added

=ROUND(E11,1)=0.7

rounding E11 to 1 decimal place makes it evaluate as TRUE

"Mike H" wrote:

Hi,

If you format E10, E11 & E12 to a number with lots of decimal places you'll
finf that E10 is 0.7 but E11 & E12 are actually 0.6999999999999999 so hence
the evaluation as false when tested for =0.7.

The reason for this is in the way Excel does arithmetic and there being no
precise binary equivalent of 0.7. For a full explanation have a look here

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

Mike

"Raj" wrote:

Hi,

Please help in understanding this:

Cells d10,d11,d12 have the value 10.7 in them
Cell e10 has the value .7 directly input
Cell e11 has the value .7 derived by the formula =d11-int(d11)
Cell e12 has the value .7 dervied by the formula = e11-trunc(e11)
When values in cells e10,e11,e12 are tested for =.7, cell f10 shows
true, f11 and f12 show false. Why?

Thanks in Advance for the Help.

Raj

Hi,

If you expand the decimals in F11 and F12, the result is 0.699999999999999.
So the FALSE answer is correct. The reason for the answer not being 0.7 is
conversion of numbers from decimal and binary and vice versa.

To solve this problem, you may use the round function =ROUND(E10,0)=0.7

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Raj" wrote in message
...
Hi,

Please help in understanding this:

Cells d10,d11,d12 have the value 10.7 in them
Cell e10 has the value .7 directly input
Cell e11 has the value .7 derived by the formula =d11-int(d11)
Cell e12 has the value .7 dervied by the formula = e11-trunc(e11)
When values in cells e10,e11,e12 are tested for =.7, cell f10 shows
true, f11 and f12 show false. Why?

Thanks in Advance for the Help.

Raj

"Raj" wrote:
d10,d11,d12 have the value 10.7 in them
Cell e10 has the value .7 directly input
Cell e11 has the value .7 derived by the formula =d11-int(d11)
Cell e12 has the value .7 dervied by the formula = e11-trunc(e11)
When values in cells e10,e11,e12 are tested for =.7, cell f10 shows
true, f11 and f12 show false. Why?

Most decimal fractions cannot be represented exactly in the internal form
that Excel uses to represent numbers -- a standard binary floating-pointing
form. You are encountering two different approximations for 0.7.

10.7 is represented internally as
10.6999999999999,992894572642398998141288757324218 75. (The comma is my way
of demarcating 15 significant digits to the left.) When you subtract 10,
you get 0.699999999999999,28945726423989981412887573242187 5.

But 0.7 is represented internally as
0.699999999999999,95559107901499373838305473327636 71875.

You can see that they differ starting in the 16th significant digit, and the
first representation is indeed less than the second representation.

I will explain why we get two different approximations of 0.7 below.

But the important lesson here is: use ROUND prolifically when you are
dealing with numbers with decimal fractions. For example, compute
ROUND(D11-INT(D11),1), or compare ROUND(E11,1)=0.7. I prefer the first
solution whenever feasible.

Note: Although formatting might change the appearance of numbers, it does
not change the underlying value. Formatting E11 as Number with 1 decimal
place is not the same computing ROUND(D11-INT(D11),1), even though they
might look the same.

Another alternative is to set the calculation option "Precision as
displayed" under Tools Options Calculation. But since that affects all
cells that do not use the General format, that option can lead to untoward
surprises. I do not use it.

So, why do we get two different approximations of 0.7?

In a nutshell, because numbers are represented internally by 53 consecutive
powers of 2 ("bits"). With 0.7, we can use the full 53 bits to represent
0.7. But with 10.7, some of the bits are used to represent 10; so there are
fewer bits to represent 0.7. In this case, that results in a less accurate
representation of 0.7. When we subtract 10, we are left with the less
accurate representation of 0.7.

All of this seems mysterious and difficult to predict, for a number of
complicated reasons. For example, you would encounter no problem with your
original formulation if D11 contained 1.7. By coincidence, the internal
representation of 1.7 has the same approximation of 0.7 as 0.7 itself.
But I reiterate: that is coincidence.

Again, use ROUND prolifically to avoid most surprises. (But unfortunately
not all!)