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 threadlynnApr 13, 2023

@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

Show threadOwen O’Malley
@lynndotpy @b0rk one large company that I have worked with used millionths of a dollar in a long. That allows for very accurate currency conversions. No accountant would knowingly accept floating point approximations of money.
Apr 14, 2023 at 10:14amWeb
Show threadlynnApr 14, 2023

@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