An interesting (to me, at least) Excel puzzlement

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...

I'll guess that the value causing trouble is tossing off an infinite repeating result. like 3/2.

Save the divide by anythings, until the last possible operation.

If it can be avoided, don't reference cells that might produce infinite repeats

Since I don't have an Excel newer than MS's best effort (Office '97), I opened it in Open Office, where it works perfectly.

No, this spreadsheet is nothing more than a series of two-decimal numbers, manually keyed in, and totaled with the total at the top rather than the customary bottom so I could freeze the top row while scrolling.

There are no repeating decimals, and no divisions. Just a column of numbers formatted as currency. Why this generates a rounding error is what puzzles me.

tanstaafl.

Oh yes there are -- in binary. Being a repeating result is not a property of a value, it's a property of a value *as represented in a certain base*. So 2/3 is infinitely repeating in decimal ("0.666...") but very neat in base-3 ("0.2"). Similarly, 1/100, or even 1/10, is very neat in decimal but infinitely repeating in binary. Spreadsheets store currency as floating-point numbers (otherwise fractions of a cent/penny couldn't be represented), so rounding errors are to be expected.

Now of course Excel could use more smarts here, and remember somewhere that all the values were entered explicitly in decimal with two fractional digits, and perform the sum in integer cents/pence. But this sort of mixed-mode arithmetic is a more advanced feature which perhaps it doesn't have. (Perhaps, even, there's a mixed-mode type that isn't Currency?)

Or you could do the same thing "manually" by storing integer numbers of cents and using a custom format to display them looking like currency.

Peter

In binary!

Peter, thank you for the first answer I have had to this puzzlement that makes sense. Of course with 20/20 hindsight... but that's the trick, isn't it? To see beyond the obvious to the answer that subsequently becomes the obvious.

My son had given me a clue that I failed to assimilate when he told me that banks and financial institutions always stored their currency data as pennies, not dollars.cents but I didn't pursue the logic of that beyond the idea that it was how they avoided the problem, but not why the problem was thus avoided.

Thank you again.

tanstaafl.

Re: floating point -- http://floating-point-gui.de/

While constructing my home thermostat, I discovered that some moron used floating point in the Arduino library for the temperature sensor, because the sensor returns tenths of a degree.

Duh.. 30% of the flash memory space needlessly consumed just because they didn't think of simply returning the temperature in tenths of a degree (eg. 182 instead of 18.2), the way the hardware itself reports it ???

Blah. I fixed it in my copy of course, freeing up 10KBytes of precious flash memory, as well as reducing the sampling time by a couple orders of magnitude.

Cheers