# cell's value in error in Excel

Copper Contributor

# 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 (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.76 149.05 0 (134.29  ) (10.00  ) 4.76 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

5 Replies

# Re: cell's value in error in Excel

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")

# Re: 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")

# Re: cell's value in error in Excel

Thank you, I used the Round() function and it solved my problem.

# Re: cell's value in error in Excel

Thank you for your explanations.

# Re: cell's value in error in Excel

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!