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 threadRob

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

One in five genetics papers contains errors thanks to Microsoft Excel

Apr 13, 2023 at 5:54pmWeb
Show threadSam Tobin-HochstadtApr 13, 2023
@simrob @b0rk I think regardless of whether _you_ would stand behind that advice, in practice people who work with money professionally all use Excel all the time for it, including in complicated ways at large financial institutions.
Show threadsayrerApr 13, 2023

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

Show threadSam Tobin-HochstadtApr 13, 2023
@sayrer @simrob @b0rk Definitely lots of excel errors, but I don't know of any that either indicate a higher or lower rate than software, or errors due to binary floats.
Show threadsayrerApr 13, 2023

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

https://learn.microsoft.com/en-US/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result

They also have weird cases where numbers are treated as dates, and things like that.

Numeric precision in Microsoft Excel - Wikipedia

Show threadsayrerApr 13, 2023
@samth @simrob @b0rk you can also find decimal/money types in most databases. IBM always has them: https://www.ibm.com/docs/en/informix-servers/12.10?topic=esqlc-numeric-data-types
Numeric data types

Show threadtobychevApr 14, 2023
@sayrer
One source of that I've seen is the "Enron corpus" spreadsheets harvested from all the emails published after Enron went bankrupt in 2001. That collection of spreadsheets had an error in about 24 % of all reviewed documents.
@samth @simrob @b0rk
Show threadRobApr 13, 2023
@samth …yes, obviously?
Show threadSam Tobin-HochstadtApr 13, 2023
@simrob Right, and I think that situation calls for (a) humility from people who don't do that about whether maybe the people who do are actually more-expert at their area and (b) an answer to Julia's question as to why it works out.
Show threadJulia EvansApr 13, 2023

@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

Show threadNathan SorensonApr 13, 2023
@b0rk @simrob when I was working in finance the biggest problem related to rounding and spreadsheets was that numbers often were copied around between sheets, to and from proposals, etc, and those would be the displayed rounding not the underlying floating point. So not floating point's fault but more software UX and process/provenance issues.
Show threadNathan SorensonApr 13, 2023
@b0rk @simrob I basically agree that floating point has been made too big of a scapegoat and lack of version control, easy-to-ruin data provenance, and sloppy auto coercions are excel's bigger flaws.