IF-AND statement problem

[octarudin]

Dear Excel Experts,

I have a question regarding IF-AND statement in Ms. Excel. I wrote this function in these cell. Please look at table below.

Cell | Value | Result

------------------------------
U85 | =9.77-9.81 | -0.04

V85 | =IF(AND(U85>=-0.04, U85<=0.04), 0, U85) | -0.04

U86 | -0.04 | -0.04

V86 | =IF(AND(U86>=-0.04, U86<=0.04), 0, U86) |  0

 

I really do not understand why the output is different. Is there any mistake from my IF-AND statement? I am using Microsoft Office Home and Student 2021. I just want to write -0.04 <= x <= 0.04 in those cell. Please anybody help me. Thank you.

Best regards

Octarudin

[#2]

Hi @octarudin 

Your question is not clear.

You can not use this as one condition (-0.04 <= x <= 0.04) , so you have to use AND 

[#3 · Accepted solution]

@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-res... 

[#4]

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