SOLVED

# IF-AND statement problem

Copper Contributor

# IF-AND statement problem

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

3 Replies

# Re: IF-AND statement problem

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

best response confirmed by octarudin (Copper Contributor)
Solution

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

# Re: IF-AND statement problem

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

1 best response

Accepted Solutions
best response confirmed by octarudin (Copper Contributor)
Solution

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