Computer arithmetic

[Jean-Pierre A. Radley]

Kenneth Brody propounded (on Thu, Jun 13, 2013 at 05:56:04PM -0400):
| On 6/13/2013 4:18 PM, Jean-Pierre A. Radley wrote:
| > I'm generating a CSV file for Excel comprising about 6500 invoices.
| >
| > Each invoice (row) has charges allocated to one or the other of six
| > category columns (e.g., goods, tax, freight...), and then there's a
| > total column.  (I'm using filePro's '$' edit for the CSV entries.)
| >
| > At the bottom, the columns are totaled, but the sum of the first six
| > columns does not equal the total in the seventh column; the error is
| > slightly different if I let filePro do all the totalling, or if I
| > use Excel SUM formulas.
| >
| > The error is typically a few thousand bucks out of a grand total over
| > six million dollars.
| >
| > Is this an inherent result of representing decimal numbers in a computer's
| > binary language?
| 
| Fortunately, although "0.10" cannot be precisely represented in binary, 
| filePro *can* represent it precisely.
| 
| I guess the question here is:  did either filePro or Excel give the correct 
| sum, and if so, which one is correct?  (And if filePro is wrong, I'd like to 
| know exactly how the calculations were done.)

Neither one was correct, they had different errors, but the same
order of magnitude.  Let me think about how to present the code.

| If you generate the file from a smaller set of records, is the sum
| still wrong?  If so, you should be able to manually calculate the
| correct value.

I will try a smaller data set, but it is precisely manual calculation
that showed me the error on the large data set.

-- 
JP