Negative number rounded

[clee]

A co-worker brought this problem to me that's got me stumped. Three 2 decimal place numbers (one of them negative) sums to 10 decimal places.
<TABLE style="WIDTH: 120pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=160 border=0><COLGROUP><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5120" width=160><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 120pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=160 height=19>25,196.9100000000000 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>550,135.8300000000000 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>(552,285.9300000000000)</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>23,046.8099999999000 </TD></TR></TBODY></TABLE>

 

[Jonmo1]

This is due to Floating Point Precision.

For explaination of this issue, see
http://blogs.office.com/b/microsoft...es-excel-give-me-seemingly-wrong-answers.aspx

I don't fully understand it myself..


But you can work around the issue using Round

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

 

[MrKowz]

Excel only has accuracy to 15 digits due to the floating point decimal system. The only way around this is to store the value as a string.

 

[clee]

Thx