Google Sheets – Why Zero is Not Equal to Zero

google sheets

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?

Best Answer

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.

Related Topic