If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Summation: Difference in Decimal
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 |
#2
|
|||
|
|||
Summation: Difference in Decimal
"Mels" wrote:
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? 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 ----- "Mels" wrote in message ... 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 |
#3
|
|||
|
|||
Summation: Difference in Decimal
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mels" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|