Forum Discussion
ERROR RESULT OF FORMULA
I was work using basic formula but i can't finish my work cause the result formula was error / wrong,
i need your help, i marked yellow colour.
Thank you
Typical binary arithmetic anomaly.
The work-around is: whenever a calculation involves or results in numbers with decimal fraction, and we want the result to be accurate to some number of decimal places, explicitly round the calculation to that number of decimal places -- and not to an arbitrary number of decimal places (e.g. 10).
For example, =ROUND(B1-A1, 2)
BTW, you might want =ROUND(B3-A3, 2) as well.
Even though the result appears to be exactly 14.95 due to arbitrary Excel formatting limitations, it is not. Consequently, for example, ISNUMBER(MATCH(14.95, C3, 0)) and C3-14.95=0 return FALSE, even though C3=14.95 returns TRUE (!).
The latter inconsistency is specific to the Excel implementation (and some work-alike apps). It is not typical of the binary arithmetic standard.
-----
The root cause of the original problem is: most decimal fractions cannot be represented exactly in the binary form (64-bit binary floating-point) that Excel (and most applications) chose to represent numbers internally.
Consequently, most decimal fractions are approximated in binary.
Moreover, 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 (!).
