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

# 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.