Excel Calculation Issue

[azbasketcat]

I have an interesting issue I just found in one of my Excel workbooks, I was hoping one of the brilliant minds on Mr. Excel could shed some light on this.

I have three values in A1, A2 and A3. All three of these values were derived from formulas in another worksheet, but I pasted the values in a blank workbook and was able to replicate the issue. Essentially, A2 was derived from subtracting A3 from A1. I am trying to understand why Result 1 does not equal zero, even though it should as proven by Results 2 and 3.

Ultimately, I can deal with this by using Round(), but was curious if anyone has any thoughts. Thanks - Randy


[TABLE="width: 445"]
<tbody>[TR]
[TD]A1:
[/TD]
[TD="align: right"]25201.0862309080000
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2:
[/TD]
[TD="align: right"]23334.4417737846000
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A3:
[/TD]
[TD="align: right"]1866.6444571233500
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Is Zero
[/TD]
[/TR]
[TR]
[TD]Result 1:
[/TD]
[TD="align: right"]A1-A2-A3 = 0.0000000000018
[/TD]
[TD][/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD]
Result 2:
[/TD]
[TD="align: right"]A1-A3-A2 = 0.0000000000000
[/TD]
[TD][/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD]
Result 3:
[/TD]
[TD="align: right"]A1-SUM(A2:A3) = 0.0000000000000
[/TD]
[TD][/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Take a look at
Floating-point arithmetic may give inaccurate results in Excel

M.

 

[palonso]

Excel carries many decimal places depending on how 'exact' the math is. Depending on how you perform your calculation, excel (and other programming languages), pick up numbers after about 8-10 decimal places. For instance, 0.2 is exact while 0.2 = 2/10 is an approximation.

 

[azbasketcat]

Interesting. Thank you for both of your responses.