8

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?

  • 2
    Have you never encountered the joys of floating point math? – ale Jul 10 '15 at 23:15
  • 1
    Yes I did (in the worse way) with Javascript. But don't Google Spreeadsheets math computation occurs server side? – bruno Jul 10 '15 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 Jul 10 '15 at 23:21
  • 1
    Perhaps you could check if it's less than or equal to the smallest number. – aparente001 Jul 11 '15 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 Oct 19 '18 at 7:51
6

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.

  • 1
    I mentioned "server-side" because Google could use arbitrary precision decimal numeric type from their favorite programming language within their servers. – bruno Jul 13 '15 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 Jun 17 '18 at 9:29
  • My solution was to =FLOOR(B3+C2, 0.01) for working with currency – NonlinearFruit Jul 28 '18 at 3:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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