Forum Discussion
cell's value in error in Excel
That is normal. Computer calculation relies on binary arithmetic. Relatively few decimals have exact binary representations so small errors accumulate during floating point calculation (integers are exact). Tests for equality are then likely to fail so, instead, it is normal to test that the percentage error is extremely small, e.g.
= IF(ABS(100 * (calculated-input)/calculated) < 0.000000001, "ok", "error")
Be very careful how you use ROUND. The logic of "I know the two amounts are essentially the same, so by ignoring fractions of a cent, they will be exactly the same" is sound and the test will succeed.
The catch comes if, later in the calculation, you multiply by a million. Rounding will have removed all 10ths of a cent as insignificant. However, once you have multiplied by a million the discarded tenths are now $1000s. Rounding can create as many problems as it solves!