I mean, it is a needlessly ugly (and confusing) way to do things, but it seems that’s all we have unless we use ROUND (and I’m not sure off the top of my head whether that may still cause problems, but I don’t think rounding to the hundredths should still be prone to floating point error, where it rounds off in the wrong direction or something.) Still ugly, though.
I still wonder… what if you want to divide a payment by exactly 3, or an interest payment by 20?
123.45 / 3 = 41.15 exactly, but 123.46/3 = 41.15333… . Sure, you could work with a fixed precision, but why not have more digits available for intermediate results if you can use them?
The different available rounding modes are predictable; what do you mean by the “wrong direction”?
I was thinking in terms of the summation in the OP, which shouldn’t be a problem there, as you won’t get anything with a decimal component if you multiply everything by 100.
All this goes back to my question to the OP about what the numbers represent. Not just currency versus, e.g. temperatures, but are they sums, products, results of divisions, etc. Only armed with that can we talk about how much precision is real in the underlying FP numbers and how much is artifact. And then we can figure out how to do the various math steps in a way that maintains real precision and avoids propagating false precision.
Most likely all this is way above the OP’s head, but if they want a correct answer every time, they need to have a process that’s correct by design, not one that gets lucky via rounding most of the time.
And all of which needs to fight with Excel’s “helpful” behavior of trying to sweep most of this under the rug most of the time because most people don’t care enough (or often enough) for it to matter. The fact a lot of people work with spreadsheets all day and don’t understand the difference between the data as displayed (i.e. formatting) and the data as stored or computed demonstrates the knowledge gap Excel is trying to bridge automatically.
Excel can easily display numbers as currency, without changing how they are stored internally. (When I tried the OP’s numbers in Excel, I tried reformatting them in a couple of different ways, including as currency, but that did not affect the not-quite-zeroness of the sum.)
If Excel has a way of storing numbers internally as currency, I don’t know how.
Not only did HP calculators use binary-coded decimals, but they did it using their own custom-designed implementation where “1” was 0001, as expected, but “9” wasn’t 1001 (I don’t remember what it was instead-- it might have been 1111). Apparently this shuffling of digits let them occasionally save one bit operation, in their internal implementation of the arithmetic operators.
There’s an enormous number of BCD formats. From Wikipedia, this is just the part of the list that fit on my screen:
This has its own issues.
Take N. Divide it by a million. Multiply the result by a million. You should get N. Instead you’ll likely get 0.
You have to be careful doing math in a computer, no matter what format your data is in and what operations you are doing.
I was answering in the narrow scope of the OP, as I clarified in a later post.
Very easy. Takes 2 words in COBOL.
Even in 1958 FLOW-MATIC, Grace Hooper had it available.
I took the numbers in the OP, pasted them into excel and did a sum. It gave the same answer as the OP. I then formatted the numbers as currency and the sum changed to 0.
Was the result stored in a cell that was formatted as currency?
I highlighted the cells and it shows the sum on the bottom bar. I’ve added a cell with SUM() and it also shows €0.00 as the result. Office 365 btw.
Thanks. I did a very quick google to see if the format affected the calculation and didn’t get a clear answer either way. I hope to have more time later.
Fixed point arithmetic (using integers scaled by the number of decimal places internally) can be a good choice for this kind of thing subject to the considerations engineer_comp_geek pointed out above. No internal format is a replacement for using your head.
ITYM Grace Hopper.
More weirdness. according to this microsoft link adding a formatting to a cell doesn’t change the value stored.
https://www.officetooltips.com/excel_2016/tips/how_to_avoid_misleading_numbers.html
I performed a little experiment. I took the numbers and pasted them into excel. I added a cell with the SUM() of the cells I pasted in. It shows 1.67-12. I then changed the formatting of the pasted cells to 2 decimal places. The value in the SUM cell didnt change. I then added another cell with the same formula as the SUM cell. It displays 0. So i have 2 cells with the same formula showing different results.
What is the formatting of the cell with the second SUM formula? What was the formatting of the first SUM formula before you changed?
This page
does not mention anything special about currency—just standard data types, which include integers and 64-bit binary floating-point numbers.
It also goes on to say that all numeric worksheet cell values are stored as doubles. The context of that documentation page is about writing add-ins for Excel written in the C language.
Without knowing more, and therefore what the code “really should” be doing, and absent any evidence from the OP that some calculations are a penny off or something, it still seems there is no real problem, no accounting discrepancies can crop up: a column of numbers whose absolute values sum to 750341 is added up, using a machine precision of about 10^{-16}, getting errors on the order of magnitude of 10^{-12} or even 10^{-10}. This is as well as can be expected, so still no evidence of a true bug anywhere, and can be safely rounded off to 2 digits if is it otherwise known there are no fractions anywhere, so the choice of computer arithmetic model was not a mistake.
The only comments are: can’t hurt to use decimal instead of binary numbers (if some more recent Excel supports it), and/or integers if nothing beyond addition/subtraction (i.e. division, logarithms, etc.) is needed; also if the real data has millions or billions of numbers rather than a few dozen then maybe a format with more digits of precision (again, assuming Excel supports it) is warranted (maybe— I do not know the exact application here).