Error in calculating differences

No. It's floating point arithmetic. If you encounter this, you need to round your data to an appropriate level of precision.

https://learn.microsoft.com/en-GB/of...ccurate-result

Its a decimal value difference.

Originally Posted by Puiman

why wouldn't Excel at least generate a positive figure, such as 4.65661E-10?

Because B2>A2 is false, but B2-A2>0 is true(!).

It is a quirk of Excel: for comparison operators, Excel rounds each operand to 15 significant digits internally just for the comparison.

So B2=A2 returns TRUE, even though their binary values are not equal.

BTW, your original formula could be written more simply as =ABS(B2-A2).

Then the result would indeed be positive 4.66E-10 (approximately).

Yes, but, yes, but.... 1.15 is not an integer.

This uses integers throughout and results in a zero:

=3420000*115/100

Originally Posted by Puiman

I can't get over with the fact (?) that there are some decimals lingering out there when 3420000 x 1.15 gives you a nice whole number

It is because most decimal fractions cannot be represented exactly as the sum of 53 consecutive powers of 2, which is how Excel (and most applications) represent numbers internally.

And the binary approximation of a particular decimal fraction might vary, depending on the magnitude of the number. That is why, for example, 10.01 - 10 = 0.01 returns FALSE(!).

The binary approximation of 1.15 is exactly 1.149999999999999911182158029987476766109466552734375

Originally Posted by curiouscat408

The binary approximation of 1.15 is exactly 1.149999999999999911182158029987476766109466552734375

Did you just happen to know that??

Happy Christmas!

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.