Both Excel and Google Sheets return FALSE for this expression:
2.03 - 2 - 0.03 = 0
The vast majority of data transformation and BI tools (Power BI, PowerQuery, Tableau, etc.) return FALSE for this expression:
0.1 + 0.2 = 0.3
That's because they use floats instead of decimals and that introduces subtle errors in data. These errors usually never get noticed because everyone doesn't expect errors in basic math. It's a mystery to me why most commercial software intended for business and financial calculations don't use fixed point decimals. My post about this: https://www.linkedin.com/feed/update/urn:li:activity:7028101...
PS. If you design software that works with money amounts, always use fixed point decimals. Don't use floats, it's just wrong!
Paging Colin Percival to talk about picodollars. The medium strength advice on this topic is to use integer math with cents as your unit. Colin’s advice is to choose the smallest possible unit you can that will avoid overflow (I think) hence why he prices tarsnap in picodollars: https://www.tarsnap.com/picoUSD-why.html
This is something people are living with because it's very rare to use exact equality tests on floats in BI applications to begin with. Far more people want to look at the sum of order amounts, or at orders where the amount is within a certain range, than at orders where amount is exactly equal to some random float.
> If you design software that works with money amounts, always use fixed point decimals. Don't use floats, it's just wrong!
Don’t write with such certainty! Decimal math is great advice for many/most situations, but what if you have a LOT of numbers and not a lot of time? That big number crunching GPU is not available if you take this approach.
Numerical Methods was the most difficult CS course I took in university and also the one I did the worst in. And of course it was an elective, or else they wouldn’t have graduated many people at all. If you’re doing a lot of number crunching stuff, maybe you should ask people that know how to number crunch to design your system so it has the smallest errors :)
The really surprising* thing is that Google Sheets uses floats for everything. Back in the day, I was using Sheets to do some statistics about (IIRC) kernel ASLR on macOS, and I was surprised to see kernel pointers ending in impossible digits. Of course only after I'd wasted 2 hours on it.
Boy, did I file a pissy bug with the Sheets team that day, and then requested an Excel install I never let go of since that day.
* I guess it's maybe not surprising to js developers, but don't most modern browsers have integers by now?
Fixed point fails for just about any financial calcs beyond simple addition. Try doing common compound interest calcs for example, and you'll get much worse answers.
The correct answer is to use floating point and to understand it and numerical software before doing it. If you don't have a decent understanding of numerical analysis, don't write important numerical software.
Even if you set the data format to 'currency' it still returns false (in Google Sheets). I realise they probably want consistency but it's weird they don't have an option to use a decimal type.
> It's a mystery to me why most commercial software intended for business and financial calculations don't use fixed point decimals
All the reporting software I have seen in banks use decimals for adding numbers.
If you are adding small numbers together, those errors are negligible and get rounded out in the result (you usually can't pay an amount with more than two decimals). It's only a problem if somehow you are doing some calculations that need to be exact on amount large enough that the float rounding starts affecting your pennies.
Financial reporting has materiality thresholds, no one cares about pennies if the size of a balance sheet is trillons, the materiality will likely be in millions, not the least because the numbers will be shown in millions in the financial report and the numbers won't be additive because of rounding) and for a BI tool a number with 12 digits is unreadable and too much information to be useful.
If you are doing pricing, also no one really cares about pennies on a 1 million payment.
> PS. If you design software that works with money amounts, always use fixed point decimals. Don't use floats, it's just wrong!
Well, it depends. If all you do is add and substract numbers, ok, and that's what they typically do. If you need to do any other calculation (and most financial software does), this will bite you as percentages and ratios will be rounded aggressively, and multiplying large amounts will overflow.
I've said this before, but I wish python and other high level languages defaulted to decimal for the literals, and made float something you had to do explicitly. My reasoning behind this is that floating point math is almost always an implementation detail, instead of what you're actually trying to do. Sure, decimal would be slower, but forcing people to use float as an optimization would remind them to mitigate the risks with rounding or whatever.
There is a very big catch---many if not most mathematical functions won't be exact anyway, so you have to round at some decimal places. Python does this with its `decimal` module: the number of fractional digits is literally a part of the global state [1]. While this allows for more concrete control over rounding, assuming that there was no such control, it turns out that the choice of radixes doesn't matter that much.
Honestly, I'd just be happy with first class language support for decimals at all.
For example, I'm a huge fan of TypeScript, but it is hamstrung by the fact that javascript only supports a single `number` type (and, recently, `bigint`). Worse is the effect that since JSON is derived from javascript, it also has no built-in decimal type. So what happens inevitably when you want to represent stuff like money:
1. First people start using plain numbers, then they eventually hit the issues like this post.
2. Then they have to decide how they will represent decimals in things like APIs. Decimal string? Integers that represent pennies or some fraction of pennies?
3. Also, pretty much all databases support decimals natively, so then you get into this weird mash of how to not lose precision when transferring data to and from the DB.
Overall it's just definitely one of those issues that programmers hit and rediscover again and again and again. I'm surprised there hasn't been more movement towards a better language-level solution for the post popular language in use worldwide.
And then you get “why isn’t 3 × ⅓ equal to 1?” and similar questions. “Use rationals” would only postpone the issue to “Why isn’t (√2)² equal to 2?” and similar questions.
I would think that, nowadays, every child would learn that calculators do not always produce exact answers almost in kindergarten.
Also (nitpick), it’s not “float vs decimal”. “Floating vs fixed point” and “binary vs decimal” are orthogonal issues.
This smells like a good fit for Haskell, since computation is deferred until a result is demanded. I haven't tried it but I can imagine an implementation of, for example, division that would do its best to keep the numerator and denominator intact in their original formats until forced to kick out a value.
(My Haskell-fu isn't deep, but I suspect it would even be possible to write it so that, for example, multiplication of two division operation expressions multiplied the numerators together instead of doing divide -> divide -> multiply...).
Common Lisp defaults to ratios of integers for all precise calculations, which is nice other than ending up with results like 103571/20347, which is not obviously "slightly more than 5" the way that 5.090234432594485 is. It does have the advantage over decimals that e.g 1/3 can be represented precisely.
This put my daughter off of programming. When she was 7, I showed her how to use python in immediate mode, and she got it without difficulty. She even understood variables. Then one day she wanted to add prices, and she got one of these errors, and she never wanted anything to do with it again.
> I wish python and other high level languages defaulted to decimal for the literals, and made float something you had to do explicitly.
When Python originally made the choice to have literals with decimal points in them be floats, the language did not have a decimal implementation, so floats were the only choice.
I don't know if anyone has proposed changing the default now that Python does have a decimal implementation, but I suspect that such a proposal would be rejected by the Python developers as breaking too much existing code.
What would be almost as nice, and would be backwards compatible, would be introducing a more compact way to declare decimal literals, something like "0.1d".
You are conflating fixed point vs floating point and decimal vs binary, which are entirely different things. You can have decimal floating point numbers and binary fixed point.
You realize that decimal numbers also have the exact same types of rounding issues as binary numbers right? The only difference is that the former allows you to divide by both 2 and 5 cleanly, whereas the latter only lets you divide by powers of 2. If you want to divide by 3, 7, 11, or compute a square root or an exponent, using decimals is not going to save you from having to reason about rounding.
Would a "fixed-point binary-coded decimal" type be a solution here? With 64 bit values that gives you 16 digits to play with, which for "everyday numbers" seems like plenty.
Would it? I thought dealing with integers - a value, in binary - would be faster than floats - a value in binary, a decimal places value, whatever odd logic there is required to hide the leaky abstraction.
Edit: nevermind. Since the conversation was 'decimal versus float' I thought 'decimal' meant integers without floating points.
If decimals means a decimal point, I think a better suggestion would be to use integers.
a) You want rationals, not "decimals". Limiting yourself to denominators of powers of 10 is utterly stupid if you have the chance to implement a proper number type stack.
b) Floats are efficient approximations of real numbers. Trigonometry and logarithms are vastly more important than having the numbers be printed pretty, so defaulting to rationals instead of reals is quite insane.
You can already use fixed-point (AKA "decimal") values in any language which supports integer artihmetic, but you will quickly discover the two major limitations it has: your programs still need to account for precision, and the range of values which can be expressed becomes smaller as precision increases.
Do processors accelerate decimal/fixed point? I know some have offered this in the past but I’m not current on instruction sets for accelerated maths. My guess is a lot more energy goes into floating point and integer.
I don’t understand how float would be an implementation detail and not the the thing you are trying to operate on. If a programmer uses a float they are most certainly wanting to use a float?
From a deleted comment I liked here from @stabbles:
> some things can be represented in finite digits in base x but require infinite digits in base y.
Very good summary. Binary to decimal is very straightforward until fractions require infinite digits. I don’t think dec64[1] is even a tradeoff—it’s just better. The significand stays a normal binary number— but it encodes the decimal point in gasp decimal. No infinities required for the numeric language that we all think in.
I once inherited the development and maintenance responsibilities on a financial forecasting/business development app. There were lots of expected value computations in the form of adding up potential revenues multiplied by their probability of win with costs subtracted (i.e. currency values). The previous developer had used floats everywhere resulting in runoff values as seen here.
I started using decimal in all of the new features in an effort to mitigate this but it resulted in even more headache as I now had a bunch of casts floating around on top of the truncation band-aids that I had to implement for existing lengthier calculations. My plan was to refactor the whole app and SQL schema but if memory serves I got pulled onto something more pressing before I had the chance.
This was especially disappointing to me because this was all implemented in C# and T-SQL which are languages with first-class support for decimal numbers. It wouldn't surprise me if the app is still in use today with some hapless dev halfway across the country whacking these bugs as they pop up.
What Every Computer Scientist
Should Know About Floating-Point Arithmetic
One thing worth mentioning is that the IEEE 754 floating point standard is implemented in hardware in most CPUs (microcontrollers might deviate) so if you learn this stuff you’ll be set for life, as in, it doesn’t depend on the programming language you’re using.
For everyone working on tech... When your friends mock you, and make funny comments how tech is hard and often unpleasant to use this a perfect example.
This thread have multiple `techy` examples, how binary numbers works, etc. Thurth is the year is 2023 we have CPUs with billions of transistors and we still have fundamentaly basic bugs like math.
I really don't understand why languages dont default to decimals for literals..
There was a post on /r/softwaregore recently where someone showed a progress bar on Steam that said "57/100 achievements! Game 56% complete" or something like that. I snarkily commented something about naively using floor() on floating point and then moved on.
But then I thought that that may not have been the problem, fired up emacs and wrote a C program basically just saying
printf("%f\n", 57.0 / 100.0 * 100.0);
To my surprise this correctly gave 57.000000, but in python,
57 / 100 * 100
Gave 56.999... anybody know what was up here? Different algorithms for printing fp?
I have no idea what a floating point is but I still enjoyed reading the comments. I bet there are many people like me who love HN but choose not to make their presence known so as not to be downvoted (full disclosure: I've never down- or up-voted. But I do know this: on the rare occasions I remark on how funny or clever I found something, that comment usually gets downvoted).
"The short answer is that 0.1 + 0.2 lies exactly between 2 floating point numbers, 0.3 and 0.30000000000000004, the answer is 0.30000000000000004 because its significand is even."
I'd love to see an in depth exploration of exactly how float imprecision happens. Why is the error at 4e-10 and not 3? What binary magic is causing these spurious results?
We've all been told about float imprecision in a very hand-wavy way that doesn't actually explain anything at all about the problem. On its face, one would assume that any two binary values being added would have a reliable and deterministic value, but that's not how floats work. There's some magic in the stack that causes errors to creep in, which isn't something we see with other types of numerical representation. It's very easy to show and understand that 01b + 01b = 10b, but that logic somehow doesn't apply to floats.
I think it's a very interesting subject and I wish there was more discussion of the real causes rather than just "oh yeah, floats do that sometimes, just ignore it"
I really like biginteger rational types when working with typical non-scientific non-integer problems, like classical accounting. Everything continues to work without a hitch when somebody decides they need to support a tenth of a cent (gas stations), they need to support a tax like a third of a percent, they need to seamlessly mix with smaller units like satoshis, ....
It's a solution that never fails (by hypothesis, you aren't working with sin or pi or gamma or some shit), and for every practical input the denominator stays small enough that it fits nicely into machine registers. You have the bigint fallback for the edge cases, in case they're needed.
Floats IMO are only suitable when you're actually okay with approximate results (perhaps including known convergence properties so that you can reify an ostensibly floating point result into more exact values).
Unfortunately, they are not a solution to the OPs problem, which is fundamentally embedded in the architecture of computers. One has to find an appropriate representation for the needed numbers in bits and bytes.
In Python one can use fractions or decimals, if the float format is not good enough. Other options are fixed point arithmetic or arbitrary precision arithmetic. Choose one that combines the needed characteristics with the least amount of work.
0.1 in binary, is a repeating decimal - just like how 1/3 in base 10 is a repeating decimal.
So you get errors due to rounding.
This is also why it's super important in loops to never use "=" as a condition statement, but instead use "<=" (or ">="). Otherwise you might create an infinite loop.
[+] [-] dgudkov|3 years ago|reply
PS. If you design software that works with money amounts, always use fixed point decimals. Don't use floats, it's just wrong!
[+] [-] gorgoiler|3 years ago|reply
[+] [-] yafbum|3 years ago|reply
[+] [-] Kon-Peki|3 years ago|reply
Don’t write with such certainty! Decimal math is great advice for many/most situations, but what if you have a LOT of numbers and not a lot of time? That big number crunching GPU is not available if you take this approach.
Numerical Methods was the most difficult CS course I took in university and also the one I did the worst in. And of course it was an elective, or else they wouldn’t have graduated many people at all. If you’re doing a lot of number crunching stuff, maybe you should ask people that know how to number crunch to design your system so it has the smallest errors :)
PS - I’m not the person for that job!
[+] [-] ezfe|3 years ago|reply
[+] [-] t8sr|3 years ago|reply
Boy, did I file a pissy bug with the Sheets team that day, and then requested an Excel install I never let go of since that day.
* I guess it's maybe not surprising to js developers, but don't most modern browsers have integers by now?
[+] [-] SnowHill9902|3 years ago|reply
[+] [-] SideQuark|3 years ago|reply
The correct answer is to use floating point and to understand it and numerical software before doing it. If you don't have a decent understanding of numerical analysis, don't write important numerical software.
[+] [-] roxgib|3 years ago|reply
[+] [-] cm2187|3 years ago|reply
All the reporting software I have seen in banks use decimals for adding numbers.
If you are adding small numbers together, those errors are negligible and get rounded out in the result (you usually can't pay an amount with more than two decimals). It's only a problem if somehow you are doing some calculations that need to be exact on amount large enough that the float rounding starts affecting your pennies.
Financial reporting has materiality thresholds, no one cares about pennies if the size of a balance sheet is trillons, the materiality will likely be in millions, not the least because the numbers will be shown in millions in the financial report and the numbers won't be additive because of rounding) and for a BI tool a number with 12 digits is unreadable and too much information to be useful.
If you are doing pricing, also no one really cares about pennies on a 1 million payment.
> PS. If you design software that works with money amounts, always use fixed point decimals. Don't use floats, it's just wrong!
Well, it depends. If all you do is add and substract numbers, ok, and that's what they typically do. If you need to do any other calculation (and most financial software does), this will bite you as percentages and ratios will be rounded aggressively, and multiplying large amounts will overflow.
[+] [-] dec0dedab0de|3 years ago|reply
[+] [-] lifthrasiir|3 years ago|reply
[1] https://docs.python.org/3/library/decimal.html#context-objec...
[+] [-] hn_throwaway_99|3 years ago|reply
For example, I'm a huge fan of TypeScript, but it is hamstrung by the fact that javascript only supports a single `number` type (and, recently, `bigint`). Worse is the effect that since JSON is derived from javascript, it also has no built-in decimal type. So what happens inevitably when you want to represent stuff like money:
1. First people start using plain numbers, then they eventually hit the issues like this post.
2. Then they have to decide how they will represent decimals in things like APIs. Decimal string? Integers that represent pennies or some fraction of pennies?
3. Also, pretty much all databases support decimals natively, so then you get into this weird mash of how to not lose precision when transferring data to and from the DB.
Overall it's just definitely one of those issues that programmers hit and rediscover again and again and again. I'm surprised there hasn't been more movement towards a better language-level solution for the post popular language in use worldwide.
[+] [-] Someone|3 years ago|reply
I would think that, nowadays, every child would learn that calculators do not always produce exact answers almost in kindergarten.
Also (nitpick), it’s not “float vs decimal”. “Floating vs fixed point” and “binary vs decimal” are orthogonal issues.
[+] [-] shadowgovt|3 years ago|reply
(My Haskell-fu isn't deep, but I suspect it would even be possible to write it so that, for example, multiplication of two division operation expressions multiplied the numerators together instead of doing divide -> divide -> multiply...).
[+] [-] jwmerrill|3 years ago|reply
I think this is a pretty user-friendly compromise.
[0] https://docs.raku.org/syntax/Number%20literals
[1] https://docs.raku.org/type/Rat
[+] [-] aidenn0|3 years ago|reply
[+] [-] embedded_hiker|3 years ago|reply
[+] [-] pdonis|3 years ago|reply
When Python originally made the choice to have literals with decimal points in them be floats, the language did not have a decimal implementation, so floats were the only choice.
I don't know if anyone has proposed changing the default now that Python does have a decimal implementation, but I suspect that such a proposal would be rejected by the Python developers as breaking too much existing code.
What would be almost as nice, and would be backwards compatible, would be introducing a more compact way to declare decimal literals, something like "0.1d".
[+] [-] esoterica|3 years ago|reply
You realize that decimal numbers also have the exact same types of rounding issues as binary numbers right? The only difference is that the former allows you to divide by both 2 and 5 cleanly, whereas the latter only lets you divide by powers of 2. If you want to divide by 3, 7, 11, or compute a square root or an exponent, using decimals is not going to save you from having to reason about rounding.
[+] [-] mysterydip|3 years ago|reply
[+] [-] im3w1l|3 years ago|reply
But what if... what if they had a bastard child? What if we moved the point a fixed distance in decimal... and also a floating distance in binary?
The value represented would then be sign * mantissa * 2^exponent * 10^bias
With a bias of -6, you could represent every multiple of 0.000001 up to 9 billion if I did the math correctly.
[+] [-] nailer|3 years ago|reply
Would it? I thought dealing with integers - a value, in binary - would be faster than floats - a value in binary, a decimal places value, whatever odd logic there is required to hide the leaky abstraction.
Edit: nevermind. Since the conversation was 'decimal versus float' I thought 'decimal' meant integers without floating points.
If decimals means a decimal point, I think a better suggestion would be to use integers.
[+] [-] otabdeveloper4|3 years ago|reply
b) Floats are efficient approximations of real numbers. Trigonometry and logarithms are vastly more important than having the numbers be printed pretty, so defaulting to rationals instead of reals is quite insane.
[+] [-] Spivak|3 years ago|reply
[+] [-] snickerbockers|3 years ago|reply
[+] [-] fnordpiglet|3 years ago|reply
[+] [-] toolslive|3 years ago|reply
https://en.wikipedia.org/wiki/COBOL#PICTURE_clause
[+] [-] eru|3 years ago|reply
Why not rational numbers?
[+] [-] gorgoiler|3 years ago|reply
[+] [-] fsloth|3 years ago|reply
[+] [-] sfpotter|3 years ago|reply
[+] [-] adunk|3 years ago|reply
> To me, 0.1000000000000000055511151231257827021181583404541015625 + 0.200000000000000011102230246251565404236316680908203125 = 0.3000000000000000444089209850062616169452667236328125 feels less surprising than 0.1 + 0.2 = 0.30000000000000004.
[+] [-] undershirt|3 years ago|reply
> some things can be represented in finite digits in base x but require infinite digits in base y.
Very good summary. Binary to decimal is very straightforward until fractions require infinite digits. I don’t think dec64[1] is even a tradeoff—it’s just better. The significand stays a normal binary number— but it encodes the decimal point in gasp decimal. No infinities required for the numeric language that we all think in.
[1] https://en.wikipedia.org/wiki/Decimal64_floating-point_forma...
[+] [-] TheRealPomax|3 years ago|reply
[+] [-] alexjplant|3 years ago|reply
I started using decimal in all of the new features in an effort to mitigate this but it resulted in even more headache as I now had a bunch of casts floating around on top of the truncation band-aids that I had to implement for existing lengthier calculations. My plan was to refactor the whole app and SQL schema but if memory serves I got pulled onto something more pressing before I had the chance.
This was especially disappointing to me because this was all implemented in C# and T-SQL which are languages with first-class support for decimal numbers. It wouldn't surprise me if the app is still in use today with some hapless dev halfway across the country whacking these bugs as they pop up.
[+] [-] pletnes|3 years ago|reply
What Every Computer Scientist Should Know About Floating-Point Arithmetic
One thing worth mentioning is that the IEEE 754 floating point standard is implemented in hardware in most CPUs (microcontrollers might deviate) so if you learn this stuff you’ll be set for life, as in, it doesn’t depend on the programming language you’re using.
[+] [-] asicsp|3 years ago|reply
There's also a stackoverflow thread: https://stackoverflow.com/q/588004
[+] [-] shagie|3 years ago|reply
[+] [-] rafaelturk|3 years ago|reply
[+] [-] jacobmartin|3 years ago|reply
There was a post on /r/softwaregore recently where someone showed a progress bar on Steam that said "57/100 achievements! Game 56% complete" or something like that. I snarkily commented something about naively using floor() on floating point and then moved on.
But then I thought that that may not have been the problem, fired up emacs and wrote a C program basically just saying
To my surprise this correctly gave 57.000000, but in python, Gave 56.999... anybody know what was up here? Different algorithms for printing fp?[+] [-] bookofjoe|3 years ago|reply
[+] [-] JJMcJ|3 years ago|reply
[+] [-] McGlockenshire|3 years ago|reply
It's a display thing, not an algorithm thing. It rounds by default at a certain length, previously 17 digits.
https://www.php.net/manual/en/ini.core.php#ini.precision[+] [-] ColinDabritz|3 years ago|reply
https://0.30000000000000004.com/
[+] [-] rkagerer|3 years ago|reply
[+] [-] rolenthedeep|3 years ago|reply
We've all been told about float imprecision in a very hand-wavy way that doesn't actually explain anything at all about the problem. On its face, one would assume that any two binary values being added would have a reliable and deterministic value, but that's not how floats work. There's some magic in the stack that causes errors to creep in, which isn't something we see with other types of numerical representation. It's very easy to show and understand that 01b + 01b = 10b, but that logic somehow doesn't apply to floats.
I think it's a very interesting subject and I wish there was more discussion of the real causes rather than just "oh yeah, floats do that sometimes, just ignore it"
[+] [-] hansvm|3 years ago|reply
It's a solution that never fails (by hypothesis, you aren't working with sin or pi or gamma or some shit), and for every practical input the denominator stays small enough that it fits nicely into machine registers. You have the bigint fallback for the edge cases, in case they're needed.
Floats IMO are only suitable when you're actually okay with approximate results (perhaps including known convergence properties so that you can reify an ostensibly floating point result into more exact values).
[+] [-] mharig|3 years ago|reply
https://spectrum.ieee.org/floating-point-numbers-posits-proc...
https://www.cs.cornell.edu/courses/cs6120/2019fa/blog/posits...
Unfortunately, they are not a solution to the OPs problem, which is fundamentally embedded in the architecture of computers. One has to find an appropriate representation for the needed numbers in bits and bytes.
In Python one can use fractions or decimals, if the float format is not good enough. Other options are fixed point arithmetic or arbitrary precision arithmetic. Choose one that combines the needed characteristics with the least amount of work.
[+] [-] tiffanyh|3 years ago|reply
0.1 in binary, is a repeating decimal - just like how 1/3 in base 10 is a repeating decimal.
So you get errors due to rounding.
This is also why it's super important in loops to never use "=" as a condition statement, but instead use "<=" (or ">="). Otherwise you might create an infinite loop.