+3Hello,
I am building a dashboard in which I include a control formula in order to make sure the AI reads well.
However the behaviour is inconsistent.
Check formula :
IF({Amount (vat incl.)}={Amount (VAT incl.) (check)},"✅","❌")
Calcul formula
{Amount (vat incl.)}-{Amount (VAT incl.) (check)}
QUESTION
Why is one result -0 and the other 0 when the mechanics between formulas and parameters are the same ?
Best answer by Alexey_Gusev
In short, it's a common issue with floating point numbers. Computers can't store most decimal values exactly, so tiny errors happen in calculations. Even if the math looks simple, like 1.23 - 0.9 - 0.33, the result might not be exactly zero - just very close. That's why you sometimes see weird small differences like -0.0000000001 instead of 0. Always expect a little wiggle room when working with decimals in code or spreadsheets.
Well, I knew that, but that’s surprise for me - when difference is <10⁻¹⁶ it counts as equal
I tried change to Currency type - not helped.
Rounding solved the issue
+28Hey
That probably just a very small rounding issue! Try the following:
ROUND({Amount (vat incl.)}-{Amount (VAT incl.) (check)}, 2)
+25
In short, it's a common issue with floating point numbers. Computers can't store most decimal values exactly, so tiny errors happen in calculations. Even if the math looks simple, like 1.23 - 0.9 - 0.33, the result might not be exactly zero - just very close. That's why you sometimes see weird small differences like -0.0000000001 instead of 0. Always expect a little wiggle room when working with decimals in code or spreadsheets.
Well, I knew that, but that’s surprise for me - when difference is <10⁻¹⁶ it counts as equal
I tried change to Currency type - not helped.
Rounding solved the issue
+3Thanks will do the rounding !
The weird behaviour is when the user inputs the exact numbers without previous calculations. Then you’d expect more accuracy but the machine might see it otherwise.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.