12

After spending hours trying to figure out an error in my personal accounting spreadsheet, I could pin it up to one formula.

While this results in Zero

=((0,6-0,3-0,4)+(-0,3+0,8-0,4))

comparing it with Zero gives me false

=((0,6-0,3-0,4)+(-0,3+0,8-0,4))=0

What the heck am I missing?

Improve this question
5
  • 3
    Have you never encountered the joys of floating point math?
    – ale
    Commented Jul 10, 2015 at 23:15
  • 1
    Yes I did (in the worse way) with Javascript. But don't Google Spreeadsheets math computation occurs server side?
    – bruno
    Commented Jul 10, 2015 at 23:19
  • Probably. I'm afraid I don't have an answer for you. I just found it a little funny that here's another domain where 0 != 0.
    – ale
    Commented Jul 10, 2015 at 23:21
  • 1
    Perhaps you could check if it's less than or equal to the smallest number.
    – aparente001
    Commented Jul 11, 2015 at 23:50
  • "After spending hours trying to figure out an error in my personal accounting spreadsheet" - love that :D glad I'm not the only person who waste time this way ;)
    – Line
    Commented Oct 19, 2018 at 7:51

1 Answer 1

Reset to default
8

Thanks to the wonders of floating-point arithmetics, 

((0.6-0.3-0.4)+(-0.3+0.8-0.4))

evaluates to approximately -5.55e-17 in double precision. This holds in JavaScript as well as in Google Spreadsheets. It does not really matter whether the computation is client-side or server-side: what matters is the number format used. It appears that Google Sheets use double precision.

The solution is the same: avoid equality comparison for floating point numbers. In your case, changing the units by multiplying everything by 10 would avoid the problem.

Improve this answer
4
  • 1
    I mentioned "server-side" because Google could use arbitrary precision decimal numeric type from their favorite programming language within their servers.
    – bruno
    Commented Jul 13, 2015 at 18:24
  • There is a platform that uses decimal rather than binary floating point: HP48S. I worked out that the actual decimal digits are stored in packs of 4 bits, so a but wasteful as not all combinations of 4 bits are used. I remember that I experimented by changing the memory directly and even making digits more than 9.
    – Heimdall
    Commented Jun 17, 2018 at 9:29
  • My solution was to =FLOOR(B3+C2, 0.01) for working with currency
    – NonlinearFruit
    Commented Jul 28, 2018 at 3:03
  • 1
    @NonlinearFruit - Be very careful when using FLOOR() in Google Sheets as you can occasionally get the wrong result. For example, try this example: =FLOOR(0.3*3-0.9). You would expect the answer to be 0 but it produces -1 because of very tiny floating point arithmetic errors.
    – Simon East
    Commented Aug 20 at 4:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.