Forum Discussion
cell's value in error in Excel
I have Excel 2016 version 2306 build 16529 20182.
My excel converts 4.76 into 4.760000000000020000 and gives me an error
I am trying this:
| 4.76 | 149.05 | 0.00 | (134.29 ) | (10.00 ) | 4.76 | =IF(XEV782=XEQ782;0;"error") |
One of the amount of 4.76 is transformed into 4.76000000000002 and produce an error.
| 4.760000000000000000000000000000 | 149.05 | 0.00 | (134.29 ) | (10.00 ) | 4.76000000000002 | error |
How to fix this, because it does that on many of my cells. I manually type the info and really only type 4.76 in the cell.
Thanks
I have no idea why Excel would change the value if you type it in yourself. If 4.76 is the result of a formula it would be 'normal' behavior for Excel.
Since your numbers have 2 decimal places, change the formula to
=IF(ROUND(XEV782;2)=ROUND(XEQ782;2);0;"error")
or to
=IF(ROUND(XEV782-XEQ782;2)=0;0;"error")
Thank you, I used the Round() function and it solved my problem.
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")
- Thank you for your explanations.
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!
