Attached is a small spreadsheet (50 rows, two columns) that displays anomalously.

Column A is a list of currency-formatted numbers that I typed in. Column B is a copy of column B, but formatted to 12 decimals.

Row 2 displays the screen-locked SUMS of rows 3 through 50 for the respective columns.

I know that cell B2 is displaying a rounding error and that is why it doesn't show the expected $4,284.850000000000 value. My question is how does this rounding error come about when summing two-decimal numbers, and how, since all of the summed numbers in column B are in ####.##0000000000 format, how does it come up with a sum in ####.##9999999990 format?

My son who knows more about Excel than I will ever know says this is a limitation or effect of floating-point calculation, but why is floating point involved here with defined two-decimal (i.e., Currency) data?

This small spreadsheet is just an excerpt from a much larger one, where there are more than 13,000 possible places for this purported floating point error to occur, yet this is the ONLY example of it in the entire spreadsheet.

An interesting aspect of this spreadsheet is that if you change or delete just about any of the data in it, the rounding discrepancy disappears. What is so magical about this particular group of numbers that it caused this anomaly in the sums that did not happen anywhere else in the original spreadsheet?

Just in case anybody cares (no reason you should) the spreadsheet from which this is taken is a list of all credit card purchases dating back to 2005, parsed into MY expenditures and SWMBO's expenditures. The sanity check compares the sum of all my purchases plus all her purchases to the total of all purchases. It fails due to something anomalous in this particular group of purchases. I solved the problem with a bandaid fix: the new sanity check forces rounding of the totals to three* decimals before comparing. It doesn't fix the problem, but it hides it.

=IF(E2="","",IF((ROUND(F2,3)+ROUND(G2,3)=ROUND(E2,3)),"OK","error"))

Ugly, but it works.

tanstaafl.

*I won't go into why it is necessary to round to three decimals instead of two. There is a reason involving odd and even monthly subtotals divided by two...