Summation: Difference in Decimal

  • Thread starter Thread starter Mels
  • Start date Start date
M

Mels

I am computing this simple computation but when i am on the summation i got
to have a difference in the decimal places. i compare my computation with a
calculator and excel..How can i solve this simple problem. the situation is
this.

I formated the cell into currency and a two decimal places only.

Can you help me figure this problem?

Thank you
 
Mels said:
I am computing this simple computation but when i am on the
summation i got to have a difference in the decimal places.
[....]
I formated the cell into currency and a two decimal places only.
Can you help me figure this problem?
Click to expand...

These problems are quite common. They arise from the the internal
representation and arithmetic that Excel (and most applications) uses on
binary computers (viz. floating point arithmetic). Consequently, most
decimal fractions cannot be represented exactly, and small numerical
aberrations arise as a consequence.

Formatting only affects the appearance of values. To ensure that the value
itself is "accurate" to 2 decimal places, use ROUND(expression,2). For
example,

=IF(10.1 - 10 = 0.1, TRUE)

returns FALSE(!). But:

=IF(ROUND(10.1 - 10, 2) = 0.1, TRUE)

returns TRUE as expected.

Similarly, if you have a formula like:

=SUM(A1:A10)

you could write:

=ROUND(SUM(A1:A10), 2)

Using ROUND() prolifically might seem tedious. Alternatively, you could set
the calculation option "Precision as displayed" (Tools > Options >
Calculation). But I do not recommend it for several reasons.


----- original message -----
 
You have given us little to go on.

It is important to know that formatting a cell changes what is displayed but
not what is stored
Suppose we are computing after-tax prices
13.45 15.06 (formula =A1*(1+12%) copied wont the column)
14.86 16.64
15.67 17.55
49.26 (fromula =SUM(B1:B3)
Looks like the answer should be 49.25 so we are out by 1 cent/penny
But the actual stored values are
13.45 15.0640
14.86 16.6432
15.67 17.5504
49.2576

Solution: use =ROUND(A1*(1+12%),2) to round the stored values before
addition
Or use =SUM(ROUND(B1:B3,2)) to get 49.25
This is an array formula that need to be committed with CTRL+SHIFT+ENTER
There is another way: to use the option "use values as displayed" but it has
draw backs

best wishes
 
Back
Top