"Ashish_Vaidya" wrote:
While I understand the nature of fitting a
product's capabilities to a wide variety of
users and their particular needs

Actually, this problem arises for the lack of that, IMHO.


unsatisfied with the solution.

Then perhaps the Precision As Displayed option is the right thing for you.

You will need to format all cells whose values that you want rounded to some
numeric format that specifies the number of decimal places, e.g. Number or
Accounting. You might have that already.

Also, you might want to format all cells whose values that you do __not__
want rounded to General. For example, typically we want annual interest
rates and periodic rates derived from them to be "exact" (to the degree
permitted by the 64-bit floating point form). Of course, that means you lose
control over the appearance of the cell.

Then you might need ROUND only in formulas where you compare expression,
like IF(A1+B1 = C1-D1, ..., ...). That should be IF(ROUND(A1+B1,2) =
ROUND(C1-D1,2), ..., ...). Or you could just wait and see if a problem
arises ;-).


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

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