38 ms·
Both Excel and Google Sheets return FALSE for this expression: 2.03 - 2 - 0.03 = 0 The vast majority of data transformation and BI tools (Power BI, PowerQ
by dgudkov 4y ago
Both Excel and Google Sheets return FALSE for this expression:
2.03 - 2 - 0.03 = 0
The vast majority of data transformation and BI tools (Power BI, PowerQuery, Tableau, etc.) return FALSE for this expression:
0.1 + 0.2 = 0.3
That's because they use floats instead of decimals and that introduces subtle errors in data. These errors usually never get noticed because everyone doesn't expect errors in basic math. It's a mystery to me why most commercial software intended for business and financial calculations don't use fixed point decimals. My post about this: https://www.linkedin.com/feed/update/urn:li:activity:7028101503949168641/ https://www.linkedin.com/feed/update/urn:li:activity:7028101...
PS. If you design software that works with money amounts, always use fixed point decimals. Don't use floats, it's just wrong!
- paddim8 4y agokalker.xyz seems to return true
- CamperBob2 4y agoPS. If you design software that works with money amounts, always use fixed point decimals. Don't use floats, it's just wrong! Eh, doubles are fine for (most) currencies. Just don't do comparisons without appropriate epsilons. People who compoare floating-point numbers for equality are going to make other fundamental mistakes with whatever data type you force them to work with.
- worik 4y agoYes But actually, since fixed point decimals are not all ways available use the smallest unit of account, not smallest legal tender, and integer arithmetic And learn how to round
- adamwk 4y agoSmallest unit also has issues. For instance the Indonesian rupiah technically is made up of 100 sen, but the currency is so inflated nobody uses it and currency libraries behave differently (even different versions of the same library). We had a bug where different OS versions provided different values when normalizing it to a smallest unit integer. If you really don’t have access to a decimal type I think the best solution is to convert it to micro-units (price * 10^6). This is what Android does in its billing library.
- worik 4y agoUse sen. It can be transferred by bank transfer. What people use as cash is a distraction, generally
- radu_floricica 4y agoI thought that until literally two days ago. Turns out that if you sum a bunch of 0.37 (not even huge numbers, just around a few thousand) you end up with differences on the order of 10-20. Both in mysql and in Java. No, this doesn't makes sense to me either - the differences should be a LOT farther than 3 digits. And yet. You should have seen my face when debugging this.
- zeven7 4y agoI'm curious about this. Could you provide an example? Here's what I'm seeing: --- JavaScript (function() { let inc = 0.37; let times = 5000; let total = 0; for (let i = 0; i < 5000; i++) { total += inc; } console.log('expected:', inc \* times); console.log('actual:', total); })(); expected: 1850 actual: 1849.9999999997679 --- Java public class MyClass { public static void main(String args[]) { double inc = 0.37; double times = 5000; double total = 0; for (double i = 0; i < 5000; i++) { total += inc; } System.out.println("expected: " + (inc \* times)); System.out.println("actual: " + total); } } expected: 1850.0 actual: 1849.9999999997679 --- update: Changing `double` to `float` in Java yields: expected: 1850.0 actual: 1849.9778 and maybe that lines up with what you meant by "the differences should be a LOT farther than 3 digits", though it's hard to tell what you mean by "differences on the order of 10-20".
- HNDen21 4y agosame in SQL Server declare @i float =0.37,@times int = 0,@total float = 0 while @times < 5000 begin set @total+= @i set @times +=1 end select @total 1849.99999999977 or select sum(t) from( select top 5000 convert(float, 0.37) t from sysobjects a cross join sysobjects b) z 1849.99999999977
- radu_floricica 4y agosomething like, in mysql: create table t ( value double(10,2) not null ); put a bunch of values, something like 15k with maybe half of them being 0.37. Do the sum two ways: - export them in excel and do a sum - select sum(value) from t and you get two different values, with a difference of something like 15. Replace double(10,2) with decimal(10,2), and the sum(value) works correctly. I still have no idea why this happens. I could expect a tiny difference, and code like your above will indeed give differences like this, well under 1.0, but my particular scenario had differences many orders of magnitude higher. Still can't explain.
- stevoski 4y agoSomebody is wrong on the Internet today. Very wrong.
- Kon-Peki 4y agoIf I want to know the monthly payment on a $500,000 mortgage for 30 years at 6%, should I use: 1. Decimal math 2. Binary floating point math 3. Domain knowledge trumps all; it makes no difference
- gibspaulding 4y agoThen of course you'll have to work back to compute an APR for that by a crazy iterative formula outlined by the US gov. Which would you use for that process?
- meese712 4y agoirr? The library we use uses doubles https://github.com/eric-malachias/irr https://github.com/eric-malachias/irr. We also use doubles for the amortization table and doubles for everything.
- meese712 4y agoOh hey I work in mortgage! We use plain old doubles for everything and there's some rounds in there and occasionally an exotic round to the nearest 1/8th. Everything ends up matching fine where it needs to.
- wyager 4y agoCurrency quantities are inherently exact. If you're doing epsilon comparisons on currency quantities, you are making a fundamental ontological error.
- CamperBob2 4y agoNobody gives a hoot about 0.000001 cents. Round to the nearest 1/100 cent after adding or subtracting doubles, and you will be fine in 99.99999% of applications. The cardinal sin isn't using doubles for currency; it's using them without understanding either the tool or the job that you're asking the tool to perform.
- wyager 4y agoYou've clearly never worked with accountants.
- CamperBob2 4y ago[Citation needed]
- mths 4y agoYou've clearly never worked with accountants. [1] [1] https://news.ycombinator.com/item?id=34717487 https://news.ycombinator.com/item?id=34717487
- CamperBob2 4y agoI don't know what I expected
- 8n4vidtmkvmk 4y agoI've been using doubles for currency knowing full well that i probably shouldn't. for 8 years now. so far I've only noticed being off by a cent here and there. yep.. just don't care. will cost more than a penny to fix it now.
- goto11 4y agoAccounting and banking does not allow epsilons. You always need to be able to account for every last cent on every account and every transaction. And the precision you need is fixed, so there is no purpose in using floating points. I guess there are some contexts where floats are fine for monetary amounts, for example if you make economic forecasts or simulations. As long as the amounts are not real-world transactions, floats are probably fine.
- CamperBob2 4y agoYou always need to be able to account for every last cent on every account Then that's your epsilon. Or, more reasonably, one or two powers of 10 further down. Absolutely nobody cares about a millionth of a cent. A few people care about a thousandth, though. Most people get antsy if you can't pin a calculation down to the nearest cent. Floats are pretty much never fine for currency, but doubles are usually OK. There's a world of difference between a 24-bit mantissa and a 53-bit mantissa.
- goto11 4y agoThe problem is that some decimal amounts, e.g. 10 cent ($ 0.10) cannot be represented precisely using binary floating point. It doesn't matter how many bits you use since 0.1 have an infinite expansion in binary. This is exactly what the article describes and explains. 10 cent plus 20 cent does not equal 30 cent, when using binary floating point. This is not acceptable in accounting, since at one point the error may accumulate and cause an error at the size of a cent (or more).
- hinkley 4y agoI learned about binary coded decimal in school and it was the weirdest thing but is pretty good for money.
- ElectricalUnion 4y agoDoing mostly bcd math is unfortunately one of the reasons why the old style HP calculators were dog-slow. It also did not help that those old calculators like rounding values too much.
- jdmichal 4y agoNot calculators, but I did some tests a few years ago, and I'm pretty sure the BCD instructions on modern processors are no longer implemented in hardware. They were no different than using an equivalent string of other opcodes.
- ezfe 4y agoNumbers (from Apple) returns TRUE for both expressions you list
- sheetjs 4y agoUnder the hood, modern Numbers stores values in Decimal128 (16 bytes)
- deleted 4y ago[deleted]
- Kon-Peki 4y agoObjective-C has a really nice decimal math library. Last I looked (it’s been a while), Swift didn’t. It might have one by now. Years ago, I had a few apps in the App Store that made extensive use of it. It really was very nice to work with.
- arcticbull 4y agoSwift has the same functionality in the Decimal type.
- Kon-Peki 4y agoThanks! It looks like that was added in Swift 3. That was a long time ago. I guess I’m just really old ;)
- SnowHill9902 4y agoThe equality operator should not be implemented for the float type to being with. Money amounts should use integer.
- svachalek 4y agoA true decimal type is better than integer, if your language supports it.
- t8sr 4y agoDecimals are notoriously hairy to implement and it's not obvious how they should behave when they run out of precision. Integers are almost always the better choice when the decimal is fixed, such as with currency. (I guess it depends on what you mean by "true" decimal. If you meant BigNum, then sure.)
- tremon 4y agoDecimals are notoriously hairy to implement Only floating-point decimal is hairy. Fixed-point decimal is hardly more difficult than integer math.
- t8sr 4y agoIf you simply look at any of the real world implementations, you'll see it's not so easy. Rust has a few readable and educational crates that implement decimals, if you want to take a look.
- int_19h 4y agoDecimal floating point is fine so long as you constrain the exponent such that delta between two consecutive valid numbers can never be greater than 1 (that is, there should never be any implicit trailing zeroes). .NET Decimal type is a good example.
- ElectricalUnion 4y ago> Decimals are notoriously hairy to implement That's because the binary is implemented in hardware for you; and those days you can usually (but not always) trust the hardware to do the correct thing; if you have to implement it youself (say, deterministic, side-effect free math), it's also hairy.
- Kon-Peki 4y ago> If you design software that works with money amounts, always use fixed point decimals. Don't use floats, it's just wrong! Don’t write with such certainty! Decimal math is great advice for many/most situations, but what if you have a LOT of numbers and not a lot of time? That big number crunching GPU is not available if you take this approach. Numerical Methods was the most difficult CS course I took in university and also the one I did the worst in. And of course it was an elective, or else they wouldn’t have graduated many people at all. If you’re doing a lot of number crunching stuff, maybe you should ask people that know how to number crunch to design your system so it has the smallest errors :) PS - I’m not the person for that job!
- t8sr 4y agoI am almost completely certain that neither Google Sheets, nor Excel use that "big number crunching GPU" for anything float-related. Just getting the data to the GPU for a compute shader to run on it would take longer than just doing on the CPU in almost every case.
- bruce511 4y agoI'm not sure if the parent poster meant GPU or CPU. While yeah GPU has good floating point math, you probably don't use that for things like excel. On the other hand the CPU has floating point math too, and floating point math is MUCH faster than decimal math. So his point holds if you replace GPU with CPU, but his use of GPU is likely inaccurate.
- t8sr 4y agoAh, yeah. The performance of modern FPUs really kills, and it's changing a lot of the assumptions I used to have about performance. Anecdote: In state of the art physics code, it's common to use integers for coordinates [1]. Recently, I was working on a BSc. toy project, and I used ints for coordinates as is common. With both ints and floats it's important to be careful around functions like `tan` that go to infinity, but of course floats are more forgiving, so I prototyped some of the code using doubles. I ended up comparing performance and it wasn't even funny. Double precision arithmetic was anywhere between 3x (where a good int algorithm is known) all the way to 100x faster (if the int algorithm is cordic, for example) than integers. 1: Springel 2013 p. 1-82 https://wwwmpa.mpa-garching.mpg.de/gadget4/gadget4-code-paper.pdf https://wwwmpa.mpa-garching.mpg.de/gadget4/gadget4-code-pape...
- gorgoiler 4y agoPaging Colin Percival to talk about picodollars. The medium strength advice on this topic is to use integer math with cents as your unit. Colin’s advice is to choose the smallest possible unit you can that will avoid overflow (I think) hence why he prices tarsnap in picodollars: https://www.tarsnap.com/picoUSD-why.html https://www.tarsnap.com/picoUSD-why.html
- cperciva 4y agoPicodollars are awesome, but I actually do Tarsnap's accounting in attodollars since it's convenient to use a 64-bit unsigned integer to represent units of 10^-18.
- t8sr 4y agoThe really surprising* thing is that Google Sheets uses floats for everything. Back in the day, I was using Sheets to do some statistics about (IIRC) kernel ASLR on macOS, and I was surprised to see kernel pointers ending in impossible digits. Of course only after I'd wasted 2 hours on it. Boy, did I file a pissy bug with the Sheets team that day, and then requested an Excel install I never let go of since that day. * I guess it's maybe not surprising to js developers, but don't most modern browsers have integers by now?
- altruios 4y agobesides bigInt... What numbers in javascript are not actually doubles (what js uses under the hood for every number)? You can convert them from doubles to 32-ints apparently by some bitwise hacks like (I'm pretty sure they still are 'doubles' though - and it just does some rounding tricks) ``` |0 //signed >>>0 //unsigned ``` So any webapp (google sheets) will likely have problems related to floating point math.
- ElectricalUnion 4y ago> You can convert them from doubles to 32-ints You can use the native BigInt arbitrary-precision integers and ignore all "fits under this arbitrary limited bit slice" problems. You can use doubles as a integer directly, in a safe way, until you touch the 53 bits barrier (Number.MIN_SAFE_INTEGER === -9007199254740991 or Number.MAX_SAFE_INTEGER === 9007199254740991). None of those really are (modern) webapp issues.
- jkaptur 4y agoHow do Sheets and Excel differ in this regard? How did using floats cause some number to be odd?
- jdmichal 4y agoGP didn't say odd, or at least not numerically odd, but rather "ending in impossible digits". Floats have varying precision, not uniform. The closer the number is to zero, the more precision it has. There's a point where the precision is so low that it only covers whole numbers, and then a point after that where the precision is less than whole numbers. Given the context of pointers, it's quite possible that they were large enough to reach that less-than-whole-number-precision range. EDIT: For 32-bit floats, that's apparently any number above 16,777,216. Which seems surprisingly small to me. For 64-bit doubles that goes to 9,007,199,254,740,992. https://blog.demofox.org/2017/11/21/floating-point-precision https://blog.demofox.org/2017/11/21/floating-point-precision
- roxgib 4y agoEven if you set the data format to 'currency' it still returns false (in Google Sheets). I realise they probably want consistency but it's weird they don't have an option to use a decimal type.
- codetrotter 4y agoIt is my impression of most spreadsheet software that the “data format” is more about display, and not about actually strongly typed representations of the data.
- vba616 4y ago> If you design software that works with money amounts, always use fixed point decimals. Don't use floats, it's just wrong! I find it funny the gap between what computer people think finance requires and actual practice. The tax people in the US generally aren't interested in pennies any more! And when you use tax software that throws away the pennies before the final results, then your sums may very well not match the forms whose information is independently reported to the IRS, by well over a dollar. But nobody cares!
- sowbug 4y agoNo need to invent a divide between "computer people" and "tax people," whoever they are. Maybe the IRS allows small errors because bugs attributable to floating-point precision are too hard to fix.
- jdmichal 4y agoAFAIK the IRS has always been accepting of truncating / rounding values to full dollars and discarding cents. It's certainly been that way for the decades that I've been doing taxes.
- jay_kyburz 4y agoIn Australia the tax office doesn't event let you enter cents in online fields.
- vba616 4y agoMy point was that (at least some) standard US tax software rounds to dollars on intermediate results, apparently permitted and required by the IRS. ...while financial institutions send reports to clients and the IRS that have the totals rounded only at the end, meaning the things that are supposed to match don't and can't. Even though they are both in dollars, they are not consistent even to the nearest dollar.
- srcreigh 4y agoIn scheme, (= (+ (/ 2 10) (/ 1 10)) (/ 3 10)) is #t.
- jdsully 4y agoBecause perf was super important for a spreadsheet in the 90s, and now back compatibility is super important. You do not want your numbers changing in the next version of Excel.
- cm2187 4y agoI don't think he meant Excel. You can't use decimals in excel. First you will overflow immediatly (like multiply two numbers in trillons). Then your rounding will kill small amounts (like percentages). There is no alternative to floats in excel.
- jdsully 4y agoThe predecessor to excel (multi plan) used binary coded decimal. But there’s nothing stopping you from doing arbitrary precision decimal in excel except back compatibility (and all the thousands of lines of code looking for a float).
- lr1970 4y ago> PS. If you design software that works with money amounts, always use fixed point decimals. Don't use floats, it's just wrong! Fundamental reason being that powers of 1/10 in decimal fractions are not always representable by powers of 1/2 in binary. Specifically 0.1 = 0b00011001100110011... is infinitely long binary string. Truncating it to any finite bit-width like 32 or 64 bits always introduces an error.
- SideQuark 4y agoFixed point fails for just about any financial calcs beyond simple addition. Try doing common compound interest calcs for example, and you'll get much worse answers. The correct answer is to use floating point and to understand it and numerical software before doing it. If you don't have a decent understanding of numerical analysis, don't write important numerical software.
- PeterisP 4y agoWell, but the "common" compound interest calcs aren't how compound interest is actually calculated. You use floating point to calculate the amount due, but once you do, you round to a fixed point (e.g. whole cents) and that's it, that's the final truth. The interest accrued, interest due and interest paid out is a fixed point value by definition. And then for the next interest period you start with that rounded off value as the basis for calculating the next interest which compounds. So yes, if you use fixed point you obviously get different results, but you won't get correct results according to what's required by accounting standards unless you truncate to fixed point when needed. It's not that this difference is large - after all, it's about rounding off fractions of a cent - but accounting does need to be exact.
- SideQuark 4y ago> aren't how compound interest is actually calculated. Completely depends - package them in tranches to sell to secondary markets by the thousands - then you do exactly as I pointed out. Or if you're doing Monte Carlo futures projections modeled as compound interest and only need the value at a future time.... Or any of thousands of financial modeling needs.... If you're printing monthly bills for consumers then you round, but only at output, and only for viewable parts. So you cannot claim things are not computed this way - it depends on the financial application you're working on, >So yes, if you use fixed point you obviously get different results, but you won't get correct results according to what's required by accounting standards Ha - which standard are these? Care to cite them? I've been through this space a long time, and every time someone tells me there is a standard and I ask them for it they soon realize there is no gold, single standard. There are zillions of acceptable choices. There are ones for consumers, ones for intrabank, interbank, fed to bank, loans, mortgagaes, taxes, and on and on. There is no "correct results according to what's required by accounting standards ". Please cite your standards that apply to all these cases. Have you worked in finance on numerical financial software? >you round to a fixed point (e.g. whole cents) and that's it, that's the final truth Having done numerical stuff, including finance for decades, you simply write the entire codebase in floating point, being sure to do proper analysis that things handle ranges correctly. Then, and only for output, do you snap to desired observable precision. Never ever even once do you round something to make it look pretty, then jam it back into calculations.
- yafbum 4y agoThis is something people are living with because it's very rare to use exact equality tests on floats in BI applications to begin with. Far more people want to look at the sum of order amounts, or at orders where the amount is within a certain range, than at orders where amount is exactly equal to some random float.
- Out_of_Characte 4y agoIt would lead to some hilarious malpractise if anyone understood floating point and stole a billion cents. Things like this has happend and will continue if people arent aware of floating point error.
- DougBTX 4y ago> of floating point error. Worth noting that this is a risk whenever there is rounding, if this chain of logic happens with cents as ints, it is still wrong: 1 / 2 => 0 0 * 2 => 0 Therefore: 1 == 0
- dgudkov 4y agoPeople are living with it until they stumble when an innocent expression (a + b) >= c suddenly fails to work. For instance, in Excel the expression A1-B1-C1 >= 0 returns FALSE when A1=2.03, B1=0.03, and C1=2. Such an expression can be used for instance to filter records that fall within a certain range, and that filter would produce wrong results.
- cm2187 4y ago> It's a mystery to me why most commercial software intended for business and financial calculations don't use fixed point decimals All the reporting software I have seen in banks use decimals for adding numbers. If you are adding small numbers together, those errors are negligible and get rounded out in the result (you usually can't pay an amount with more than two decimals). It's only a problem if somehow you are doing some calculations that need to be exact on amount large enough that the float rounding starts affecting your pennies. Financial reporting has materiality thresholds, no one cares about pennies if the size of a balance sheet is trillons, the materiality will likely be in millions, not the least because the numbers will be shown in millions in the financial report and the numbers won't be additive because of rounding) and for a BI tool a number with 12 digits is unreadable and too much information to be useful. If you are doing pricing, also no one really cares about pennies on a 1 million payment. > PS. If you design software that works with money amounts, always use fixed point decimals. Don't use floats, it's just wrong! Well, it depends. If all you do is add and substract numbers, ok, and that's what they typically do. If you need to do any other calculation (and most financial software does), this will bite you as percentages and ratios will be rounded aggressively, and multiplying large amounts will overflow.
- mcv 4y agoThe problem is that fixed decimal types are far less standard in many programming languages. One thing I really loved about Groovy is that BigDecimal is the standard type for decimal numbers. Type `1.5` and you will have a BigDecimal rather than a float in your hands. Not very suitable for complex scientific calculations of course, but perfect for web development which is more likely to deal with money than scientific calculations.
- baybal2 4y ago[dead]