Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE

cell's value in error in Excel

Copper Contributor

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.76000000000002error

 

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

@cdnhermit 

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

@cdnhermit 

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

@Hans Vogelaar 

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

Thank you for your explanations.

@cdnhermit 

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!