today I'm thinking about the "don't use floating point for money" advice I hear all the time. It obviously has a lot of truth to it.
But -- Excel/Google Sheets uses floating point for all of its calculations, people use spreadsheets for money calculations all the time, and it generally seems to work just fine -- the results get rounded for display.
So I'm trying to figure out if there's a more nuanced guideline than "never use floating point for money".
thinking about trying to define a "safe zone" and a "danger zone" for floating point. maybe something like:
safe zone:
* all integer values (like 1.0, 234.0) behave 100% exactly the way you'd expect, UNLESS (!!!) they're more than 2^52. You can check for equality, it's fine.
* adding up ~100 numbers and rounding the result to 4 decimal places or so is going to work fine, as long as the numbers are roughly the same size
@b0rk yes, but...when you're dealing with money, why would you ever choose to use something that has precision errors when you can choose a solution that doesn't?
It seems like good advice regardless of how many bits of precision we have. Equality tests going wonky 20 digits out vs 10 digits out is still a failing test.
@masukomi @b0rk It doesn't matter what data type you use, you can't represent 100/3 exactly (modulo symbolic algebra).
Everything has precision errors on a computer so I think it's a bit of a moot point. Sure, you can do better than floats with other data types at the cost of performance, but you can also use more sophisticated algorithms such as Kahan summation, also at the cost of performance.
There's a popular story that describes a programmer having altered a program at a bank so it diverted fractions of a cent from every transaction to another account (or some variation like multiple
@paddy babby developer me, not quite out of college, was working for a company that built debit card rewards programs for banks. I using SQL Server Integration Services to put together an ETL pipeline that read over a tranche of BofA's debit card transactions and summed up the amount of rebates their account holders were owed.
I remember deciding between double and float and going with float because I didn't think we'd need the range of a double. I completely forgot about precision loss....
@b0rk This is a lovely idea. Re "all integer values,"
there are some corner cases where Float64's interact with generic data structures like maps, and sets because often Float64's equality and comparison do not obey the contract needed by data structures that hash or sort internally.
This can lead to oddities around questions like "does this collection contain this zero"?
For example, The caveat in the docs for the boxing type for Java double explains how Java makes double arithmetic meet user's expectations most of the time while inter-operating with generic collections. Java collections use .equals to compare values, not primitive ==, which means that a set can separately contain positive and negative zero and may contain one but not the other.
Julia Evans
Julian Andres Klode 🏳️🌈
masukomi
Jindra Lacko
brandizzi
Dave Hirschfeld
Thomas Depierre
Honza Machala
supersingular
Christopher Agocs
Paddy
Mike Samuelimport java.util.*;
public class TwoZeroes {
public static void main(String... argv) {
double x = Double.longBitsToDouble(
0x8000_0000_0000_0000L
);
Set<Double> someDoubles = new LinkedHashSet<>();
someDoubles.add(x);
System.err.println( // true
"x == 0: " + (x == 0.0D)
);
System.err.println( // true
"someDoubles contains x: " + someDoubles.contains(x)
);
System.err.println( // false
"someDoubles contains zero: " + someDoubles.contains(0.0D)
);
}
}
Negative zeroes might be rare in practice, but this might affect users who parse floating point numbers from a field pre-populated with a minus sign, like discount: -_____ or, iirc, when using rounding mode towards -Inf.
@b0rk I would never use any float for money - you never know when things will get weird. Just use BigInt if you are in JavaScript / Node.Js. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/BigInt
Also if you do need to add more than 100 floats, just do a tree-like summation (add neighbours, then add those results to their neighbours, etc. Minimizes loss.). For extra precision sort first, so you add like to like.
@b0rk Something I'd be curious about is how this works in practice. What are banks using? Governments?
Would you use signed integer amounts of pennies? Mils? What about interest rate calculations, or converting between currencies? Is anyone actually using the special libraries/APIs for dealing with currency? What is the largest number, realistically, anyone has to deal with? One quadrillion? Etc
@omalley i love this, thank you for sharing!!
quick back-of-the-envelope math: assuming long == signed 64bit int, that's $9,223,372,036,854 which can be accurately represented! :0
@b0rk So the safe zone really depends on what you’re trying to do, compiler settings, runtime settings, machine, etc.
I have seen one suggestion for float determinism which keeps all values in a range and rounds to a specific precision after each operation. It’s essentially like 64-bit float but only utilising the 58 of the bits you know will be accurate on all platforms (the frequent rounding prevents cumulative errors cascading into more significant bits).
I expect cumulative errors cascading into more significant bits would be a problem for money with 64-bit float too. Although this may be fine if you rounded after every operation and the numbers weren’t too big. At some point there’s just more representable numbers and fewer edge cases to worry about with fixed-point math.
@b0rk It also comes from before decimal64 and decimal128.
(I don't know why they even bothered with decimal32.)
@b0rk that is about half of what I was going to say; 32bit float used to be really common, because people were afraid (sometimes justified, sometimes not) 64 bit was too resource hungry.
But the other half is guard bits: 20 or more years ago, there were still some cpus that provided floating point without guard bits, and the lack of guard bits causes errors to grow much faster. (This created some problems for some console video games I worked in that era, but fortunately no money involved!)
@b0rk Anecdotally, I've heard that people who do Monte-Carlo style financial projections intentionally ignore that advice. For example, to compute a projected range of returns to describe the risk associated with a financial instrument or proposed asset allocation.
I think the reasoning is that, since hardware acceleration for 754 is substantially better, being able to do a greater number of iterations in the same amount of time contributes more to the accuracy of the results than something like using 854-based decimals would.
Leifur Halldór Ásgeirsson
Ben Houston
lynn
eviljonny
Owen O’Malley
🌈 Andrew ☄️
Daniel
Kevin Teljeur ❄️
Alex White
mathew
llewelly
Edward L Platt
Mark Asbach