View Single Post
  #6  
Old March 19th, 2010, 01:40 AM posted to microsoft.public.excel.worksheet.functions
Fred Smith[_4_]
external usenet poster
 
Posts: 2,386
Default subtraction returning incorrect calue

Why do you need more than 15 digits of precision? You can calculate the
distance to the sun to the nearest micrometre in 15 digits. What application
can need more precision than that?

Regards,
Fred



"Ashish_Vaidya" wrote in message
...
While I understand the nature of fitting a product's capabilities to a
wide
variety of users and their particular needs, it becomes terribly
inefficient
to try and re-write all of my formulas to a ROUND function due to this
phenomena.

Thank you for your answer as it answers my question, yet unsatisfied with
the solution.
--
Ashish


"Joe User" wrote:

"Ashish_Vaidya" wrote:
when I subtracted two numbers [...], why would I
receive an answer that comes back as
x,xxx.xx4999999999900000000000000000?


FYI, it does not make sense to format more 15 "significant digits" --
that
is, 15 digits starting with the first non-zero digit on the left.


Has anyone else run into this issue?
How did you fix it?


Yes, this is very common. "Everyone" runs into this issue at some point.

The short answer is: always explicitly round expressions that involve
values that might have decimal fractions. Typically, use the ROUND
function.

For example, IF(10.1-10=0.1,TRUE) returns FALSE(!). But
IF(ROUND(10.1-10),1)=0.1,TRUE) return TRUE, as expected.

(Alternatively, you might use the Precision As Displayed calculation.
But
that can result in surprising results. If you choose to try PAD, be sure
to
make a backup copy of your Excel file first. However, note that PAD will
not
help in the example above.)

The longer answer can become quite involved. In short, Excel stores
numbers
and performs arithmetic using a standard form called (IEEE 754) 64-bit
floating point. For overwhelming details, see
http://support.microsoft.com/kb/78113.

But the point to note is: most decimal fractions cannot be represented
exactly.

Instead, they are approximated by the sum of 53 consecutive powers of two
("bits"). Sometimes this results in different representations for the
same
decimal fraction. For example, 10.1 is represented internally as exactly
10.0999999999999,996447286321199499070644378662109 375, whereas 0.1 is
exactly
0.100000000000000,00555111512312578270211815834045 41015625.

The whole thing is complicatd by the fact that Excel has implemented some
heuristics to try to mitigate the effects of these differences.
Unfortunately, the Excel implementation is half-baked. It leads to even
more
confusion when, for example, subtractiing some numbers results in exactly
zero, but subtracting other "similar" numbers does not. Moreover, the
half-baked implementation can have "impossible" results; for example
IF(A1=A2,TRUE) might return TRUE, but IF(A1-A2=0,TRUE) might not.


----- original message -----

"Ashish_Vaidya" wrote:
We have a spreadsheet that subtracts one number from another. We did
not
understand why Excel is rounding the way it did so we decided to add
decimal
places until we see the value change. For instance, one of our number
was
going out to x,xxx.499999999999999999999999999999900000 while the
other
number was x,xxx.5000000000000000000000000000000000. When we rounded
the
numbers, one rounded up while the other rounded down. This is fine.
Now
that we fixed the formula to TRUNC instead of ROUND, I thought the
issue
would go away but now when I subtracted two numbers that were like
x,xxx.xx000000000000000000000000 and another number that is also
x,xxx.xx000000000000000000000000, why would I receive an answer that
comes
back as x,xxx.xx4999999999900000000000000000? This doesn't make sense
to me.
Has anyone else run into this issue? How did you fix it?

--
Ashish