Julia EvansApr 13, 2023

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".

Show threadJulia EvansApr 13, 2023

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

Show threadJulia EvansApr 13, 2023
I think it's interesting to talk about floating point's "safe zone" (things you can do with floating point that are Completely 100% Fine Actually) because I think sometimes folks see that floating point is weird and kind of... overreact and treat it as a Magical Thing that could unexpectedly break at any time.
Show threadJulia EvansApr 13, 2023
someone pointed out that this "never use floats for money" advice probably comes from the time of 32-bit floats, which have WAY less precision than 64-bit floats (8 digits instead of 16!) and are VERY VERY bad to use for money: you start losing 1 cent of accuracy around $100,000!!
Show threadChristopher AgocsApr 13, 2023
@b0rk 🎵Guess who got an angry email from Bank of America about that in 2011 or soooooooo🎵
Show threadPaddyApr 13, 2023
@agocs there's a story here
Show threadChristopher AgocsApr 13, 2023

@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....

Show threadChristopher Agocs
@paddy .... until someone on the BoA side checked and was like "hey, this is a hundred dollars off"
Apr 13, 2023 at 8:28pmWeb
Show threadPaddyApr 13, 2023
@agocs lolol okay that makes more sense, I was imagining you were a consumer of BoA and they were sending you angry emails about floating point precision and I imagined for that to be true there must be something wild going on
Show threadChristopher AgocsApr 13, 2023
@paddy Tale as old as time / song as old as rhyme / Jr. dev makes mistake
Show threadPaddyApr 13, 2023
@agocs that's basically the job, in fairness