# Floating point madness **URL:** \ **Category:** Factual Questions\ **Created:** [November 30, 2022, 1:38am UTC](https://boards.straightdope.com/t/floating-point-madness/975770 "2022-11-30T01:38:31Z")\ **Posts on this page:** 20\ **Page:** 2
**Author:** ![pulykamell](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/pulykamell/32/3166_2.png) [@pulykamell](https://boards.straightdope.com/u/pulykamell)\ **Post date:** [November 30, 2022, 2:59pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/21 "2022-11-30T14:59:41Z")
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. ---
**Author:** ![DPRK](https://avatars.discourse-cdn.com/v4/letter/d/4491bb/32.png) [@DPRK](https://boards.straightdope.com/u/DPRK)\ **Post date:** [November 30, 2022, 3:02pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/22 "2022-11-30T15:02:12Z")
> [@pulykamell](#): > > Stupid question – can we not multiply everything by 100 (and cast as an INT?) and then, with the final calculation complete, divide by a hundred? 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? > [@pulykamell](#): > > 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. The different available rounding modes are predictable; what do you mean by the “wrong direction”? ---
**Author:** ![pulykamell](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/pulykamell/32/3166_2.png) [@pulykamell](https://boards.straightdope.com/u/pulykamell)\ **Post date:** [November 30, 2022, 3:04pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/23 "2022-11-30T15:04:46Z")
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. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [November 30, 2022, 3:12pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/24 "2022-11-30T15:12:29Z")
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. ---
**Author:** ![Thudlow\_Boink](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/thudlow_boink/32/320_2.png) [@Thudlow\_Boink](https://boards.straightdope.com/u/Thudlow_Boink)\ **Post date:** [November 30, 2022, 3:46pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/25 "2022-11-30T15:46:28Z")
> [@BigT](#): > > If this is money, I believe Excel has an option to encode things as money/currency, which might use a fixed decimal. 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. ---
**Author:** ![Chronos](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/chronos/32/134_2.png) [@Chronos](https://boards.straightdope.com/u/Chronos)\ **Post date:** [November 30, 2022, 11:41pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/26 "2022-11-30T23:41:24Z")
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. ---
**Author:** ![Dr.Strangelove](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dr.strangelove/32/6613_2.png) [@Dr.Strangelove](https://boards.straightdope.com/u/Dr.Strangelove)\ **Post date:** [November 30, 2022, 11:57pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/27 "2022-11-30T23:57:19Z")
There’s an enormous number of BCD formats. From Wikipedia, this is just the part of the list that fit on my screen: [![](https://i.imgur.com/nTj8tC6.png) ](https://i.imgur.com/nTj8tC6.png) ---
**Author:** ![engineer\_comp\_geek](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/engineer_comp_geek/32/504_2.png) [@engineer\_comp\_geek](https://boards.straightdope.com/u/engineer_comp_geek)\ **Post date:** [December 1, 2022, 12:40am UTC](https://boards.straightdope.com/t/floating-point-madness/975770/28 "2022-12-01T00:40:58Z")
> [@pulykamell](#): > > Stupid question – can we not multiply everything by 100 (and cast as an INT?) and then, with the final calculation complete, divide by a hundred? 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. ---
**Author:** ![pulykamell](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/pulykamell/32/3166_2.png) [@pulykamell](https://boards.straightdope.com/u/pulykamell)\ **Post date:** [December 1, 2022, 12:45am UTC](https://boards.straightdope.com/t/floating-point-madness/975770/29 "2022-12-01T00:45:56Z")
I was answering in the narrow scope of the OP, as I clarified in a later post. ---
**Author:** ![Tim\_T-Bonham.net](https://avatars.discourse-cdn.com/v4/letter/t/46a35a/32.png) [@Tim\_T-Bonham.net](https://boards.straightdope.com/u/Tim_T-Bonham.net)\ **Post date:** [December 1, 2022, 6:05am UTC](https://boards.straightdope.com/t/floating-point-madness/975770/30 "2022-12-01T06:05:31Z")
> [@LSLGuy](#): > > Easy to do in code. Very easy. Takes 2 words in COBOL. Even in 1958 FLOW-MATIC, Grace Hooper had it available. ---
**Author:** ![bryanmaguire](https://avatars.discourse-cdn.com/v4/letter/b/d07c76/32.png) [@bryanmaguire](https://boards.straightdope.com/u/bryanmaguire)\ **Post date:** [December 1, 2022, 9:47am UTC](https://boards.straightdope.com/t/floating-point-madness/975770/31 "2022-12-01T09:47:00Z")
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. ---
**Author:** ![74westy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/74westy/32/3950_2.png) [@74westy](https://boards.straightdope.com/u/74westy)\ **Post date:** [December 1, 2022, 1:23pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/32 "2022-12-01T13:23:22Z")
Was the result stored in a cell that was formatted as currency? ---
**Author:** ![bryanmaguire](https://avatars.discourse-cdn.com/v4/letter/b/d07c76/32.png) [@bryanmaguire](https://boards.straightdope.com/u/bryanmaguire)\ **Post date:** [December 1, 2022, 1:43pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/33 "2022-12-01T13:43:15Z")
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. ---
**Author:** ![74westy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/74westy/32/3950_2.png) [@74westy](https://boards.straightdope.com/u/74westy)\ **Post date:** [December 1, 2022, 2:00pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/34 "2022-12-01T14:00:07Z")
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](https://boards.straightdope.com/t/floating-point-madness/975770/28). No internal format is a replacement for using your head. ---
**Author:** ![pjd](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/pjd/32/3009_2.png) [@pjd](https://boards.straightdope.com/u/pjd)\ **Post date:** [December 1, 2022, 6:06pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/35 "2022-12-01T18:06:59Z")
> [@Tim\_T-Bonham.net](#): > > Grace Hooper ITYM Grace Hopper. ---
**Author:** ![bryanmaguire](https://avatars.discourse-cdn.com/v4/letter/b/d07c76/32.png) [@bryanmaguire](https://boards.straightdope.com/u/bryanmaguire)\ **Post date:** [December 2, 2022, 11:33am UTC](https://boards.straightdope.com/t/floating-point-madness/975770/36 "2022-12-02T11:33:55Z")
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](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. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [December 2, 2022, 4:00pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/37 "2022-12-02T16:00:19Z")
What is the formatting of the cell with the second SUM formula? What was the formatting of the first SUM formula before you changed? ---
**Author:** ![DPRK](https://avatars.discourse-cdn.com/v4/letter/d/4491bb/32.png) [@DPRK](https://boards.straightdope.com/u/DPRK)\ **Post date:** [December 2, 2022, 6:28pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/38 "2022-12-02T18:28:57Z")
This page > **[Data types used by Excel](https://learn.microsoft.com/en-us/office/client-developer/excel/data-types-used-by-excel)** > > Office developer client VBA reference does not mention anything special about currency—just standard data types, which include integers and 64-bit binary floating-point numbers. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [December 2, 2022, 7:02pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/39 "2022-12-02T19:02:50Z")
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. ---
**Author:** ![DPRK](https://avatars.discourse-cdn.com/v4/letter/d/4491bb/32.png) [@DPRK](https://boards.straightdope.com/u/DPRK)\ **Post date:** [December 2, 2022, 9:02pm UTC](https://boards.straightdope.com/t/floating-point-madness/975770/40 "2022-12-02T21:02:02Z")
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). [Previous page](https://boards.straightdope.com/t/floating-point-madness/975770.md?page=1) [Next page](https://boards.straightdope.com/t/floating-point-madness/975770.md?page=3)