Forum Discussion
IF-AND statement problem
octarudin This is a common problem caused by precision issues with the floating-point arithmetic method used by Excel. While you would expect the result of =9.77-9.81 to be exactly -0.04, it's actually -0.0400000000000009 in Excel. You can see this by changing the formatting of cell U85 to Number format with 16 decimal places.
One solution would be to use the ROUND function in cell U85 to round the results to 2 decimal places:
=ROUND(9.77-9.81, 2)Another solution would be to use the ROUND function with the IF/AND formula in cell V85:
=IF(AND(ROUND(U85, 2)>=-0.04, ROUND(U85, 2)<=0.04), 0, U85)For more information on this issue, including additional solutions, please see: https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result
octarudin This is a common problem caused by precision issues with the floating-point arithmetic method used by Excel. While you would expect the result of =9.77-9.81 to be exactly -0.04, it's actually -0.0400000000000009 in Excel. You can see this by changing the formatting of cell U85 to Number format with 16 decimal places.
One solution would be to use the ROUND function in cell U85 to round the results to 2 decimal places:
=ROUND(9.77-9.81, 2)
Another solution would be to use the ROUND function with the IF/AND formula in cell V85:
=IF(AND(ROUND(U85, 2)>=-0.04, ROUND(U85, 2)<=0.04), 0, U85)
For more information on this issue, including additional solutions, please see: https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result
Hi, djclements
Thanks for answering my common problem. Now, I understand why it give me different result. As you mention, it worked perfectly by using ROUND function.
Best regards,
Octarudin
