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".
@paulrosen For integer division, can't always be sure that the result doesn't need rounding, so its somewhat hinting that you should use something that provides the right rounding method for the work you are doing (rounding up or down to a specific number, rounding certain transactions in certain directions, etc)
And then additionally needing to keep track of the decimal position too. It becomes complicated and deserving of a library of functions and wrappers.
@b0rk I guess the important thing to remember is that errors (can) compound, so floating point is fine if you're not doing any complex or repeated calculations.
Time-limited budgets is probably fine, but holding accounts and calculating compounding interest and stuff, nah, just move to counting cents/cent fractions in an integer or something.
@b0rk The main situation where floating point numbers go wrong is if you're dealing with very large multiples of precise values, or if you need consistency of results when you calculate something in different ways, especially if you're using a float32.
For most organisations this isn't a problem, because we're talking about sums in the millions. For financial organisations dealing with huge transaction volumes, or very precise pricing models, it's something that needs to be considered.
@b0rk I also think the common examples around floating point causing problems in finance are a little misleading. If you're using a double precision float, the actual calculations are probably not going to be skewed by a meaningful amount. What more commonly rears its head is comparison problems.
If you perform a calculation and then compare the result to some well-defined number, a small precision error can lead to unexpected outcomes. if (x <= y) might fail because x accumulated a tiny error.
@jernej__s @gsuberland I think the "20 years ago" thing here is key -- I'd guess that floating point numbers 20 years ago were 32-bit, and 32-bit floats are SUPER different from 64-bit floats
You start getting money inaccuracies in 32-bit floats at around $100,000. But in 64-bit floats, you have to go up to ~$1,000,000,000,000,000 to see similar issues (1 quadrillion dollars)
@b0rk @jernej__s @gsuberland For the love of good engineering, please stop thinking money is integers.
If you want to be precise on two digits, you need four digits. This brings you down to 100 billion.
Not that high of a number any more.
@dcoderlt @gsuberland @b0rk Wow what a great read! Filled with lovely tidbits like, "Even common decimal fractions, such as decimal 0.0001, cannot be represented exactly in binary. (0.0001 is a repeating binary fraction that has a period of 104 bits)."
104 bits! You can't just casually drop something like that in the middle of a text without further comment, but there it is.... ๐
my read on this help article is that a) excel is a generic numbers tool, and b) Msft paid someone to think very carefully about the failure modes
but if youโre a random developer adding up invoices,
a) you have a much narrower set of requirements & can tailor the data structure accordingly, and b) you might not have time to think very carefully about floats
@b0rk It doesn't specifically state "Excel" but VBA's currency type is a fixed point 64 bit integer. Maybe if you specify "currency" as the column it uses this math?
If you have the cell set as the accounting or currency vba scripts by reading from a sheet do get the 64bit fixed point currency type
@b0rk I prefer to think of it as โtry and store the smallest unit possibleโ for money.
Sure I could store pounds and pence; but easiest to store pence and do some formatting.
@b0rk Excel also admits that date formatting significantly skewed genetics research, but won't fix it because "the geneticists are holding it wrong."
https://timharford.com/2021/07/the-tyranny-of-spreadsheets/
> Or say youโre a genetics researcher typing in the name of a gene such as โMembrane Associated Ring-CH-Type Finger 1โ, or March1 for short, or perhaps the Sept1 gene. You can imagine what Excel does next. One study estimated that 20 per cent of all genetics papers had errors caused by Excelโs autocorrect.
@b0rk Plenty of domains, like various kinds of financial analytics, can happily use floats for money. Biggest issue is when you're actually dealing with real dollars and cents (or whatever the atom of your subject currency is) - you're rounding out real money.
I've taken to saying "don't use floats for accounting"' instead
@b0rk the flip side to this is the Buffett Overflow from a couple of years ago.
I think the important takeaway is "use whatever numeric format works".
64-bit integers with units of hundredths of a cent is probably the "bestest" we have now, but that's not always going to be available
@bobayaga @b0rk last I checked nah, Excel is using ieee754 doubles.
I think one small factor is that financial *forecasting* just isn't really very precise in the first place. Small factor because forecasting isn't the main use of Excel, even though as I understand it that's what Excel was originally intended for.
Bigger factor may be that nobody actually really cares about single penny rounding errors. They're nothing compared to shrinkage.
@nelson @b0rk It depends on what you're doing. My father was an accountant for the phone company, and he'd worry about being pennies off in a billion-dollar figure. Errors of that size accumulate quickly with floating point. It's a problem (probably not a big one, but that's what accountants do) if it shows up on a corporate tax document. It's all about precision.
OTOH, if you're doing a sales projection, your audience really only cares about two (maybe three) significant digits.
@Emily_S @mikeloukides @nelson @b0rk hold it... wasn't that how Richard Pryor's character got rich in Superman 3?
@kwf @b0rk I'd like to add more to it: Never use #Excel for anything - period!
#LibreOffice #Calc does the same but better...
@b0rk My first reaction to this is kinda condescending, which is just โDonโt use excel or google sheets for money if you actually need accuracyโ and Iโm debating how much thatโs genuine advice Iโd stand behind.
Possibly losing pennies or dollars or thousands of dollars to rounding errors is gonna be small potatoes compared to other errors caused by the difficult-to-debug nature of spreadsheets (see for example https://www.science.org/content/article/one-five-genetics-papers-contains-errors-thanks-microsoft-excel).
@samth @simrob @b0rk There have been many studies showing how many spreadsheets have errors. But it's true that people use them, I like to say it's always a good sign when square people with the Thinkpads show up.
A funny thing about the JP Morgan / Frank suit is that someone at the bank noticed their customer list was the Excel maximum row count.
@samth @simrob @b0rk there have been some studies that indicate a very high error rate, but Iโm not sure how they got the data.
Floating point in Excel is a famous footgun:
https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel
and then you can dig in here:
They also have weird cases where numbers are treated as dates, and things like that.
@simrob I'm struggling to see a real world use case where you would lose even 1 cent to a floating point error in a spreadsheet
like I'm sure it exists! But I personally do not know how to make a spreadsheet that does a remotely realistic calculation that has such an error
@b0rk Never use floats for calculations with other people's money.
You won't get into a law suit if you miscalculate your own budget. If you are responsible for somebody else's money, there is a much higher standard of care, and much higher return on CYA investments.
@b0rk As I understand this advice, proper bookkeeping abhors rounding - and truncating!
Traditional money isn't infinitely divisible, you can't pay someone 1/4th of a cent - it's not allowed (in the U.S. a half-cent did exist tho).
But worse, a float will just start losing money if the amount gets too large - it has limited precision: 1e16 + 1 == 1e16.
So using a data type which will at times round or truncate behind our backs and without warning, is just incompatible with sound bookkeeping?
@b0rk Also for context, I think this advice dates from when you ran the risk of a float being relatively small.
A single precision float (32 bits) only has 24 bits of precision, which is small enough that you will run into trouble with real life amounts pretty quickly.
Julia Evans
Walt Mankowski
Paul Rosen
Nelson Minar
Daniel Lo Nigro ๐ฆ๐บ
/me
Summer
@
Graham Sutherland / Polynomial
Jernej Simoncฬiฤ ๏ฟฝ
Gasper Zejn
DCoder ๐ฑ๐นโค๐บ๐ฆ
Shane Kerr
filipa mv
Chad Geidel
Nate
Toby
Dan
Jordi Rosell
chrysn
Drew ๐
Howard Chu @ Symas
Benny Powers ๐จ๐ฆ๏ธ๐ฎ๐ฑ๏ธ
renee on the internet
Random Damage ๐ป
Boba Yaga
Richard Barrell
Josh
Michael Richardson
Mike Loukides
Emily S
Kenneth Finnegan
Kevin Karhan 
Till O'Rly 
Sophie Schmieg
Rob
Sam Tobin-Hochstadt
sayrer
tobychev
Nathan Sorenson
Ryan Cavanaugh
wrosecrans
nebunez 
BjarniBjarniBjarni ๐ ๐ฎ๐ธ ๐