IF Statement Inquiry

Hi Everyone.

I am working with this IF statement =IF(ABS(B2)<=0.02,1,"ERROR") I have this formula in cell A1. The problem is, I want the IF statement to include 3 other cells C2, D2 and E2 but not sure how to accomplish this.

Thank you for the assistance.

B2, C2, D2 and E2 all have a formula in it. The formula is the results of the difference from 2 other cells. When the results in cell B2, C2, D2 and E2 are + 2 cents to - 2 cents which is an acceptable variance in this case, I would like the number 1 show up in cell A1. If the results of those cells has a greater variance of + 2 cents or - 2 cents, I would like ERROR show up in cell A1.

Thank you so much! That 1st formula worked. Much appreciated.

I have noticed using this formula =IF(AND(ABS(B2)<=0.02,ABS(C2)<=0.02,ABS(D2)<=0.02,ABS(E2)<=0.02),1,"ERROR") only works when there is a value in cell B2 which is great but I sometimes do not have a value in B2. I have also used the other suggested formula =IF(OR(ABS(B2)<=0.02,ABS(C2)<=0.02,ABS(D2)<=0.02,ABS(E2)<=0.02),1,"ERROR") and the results always shows up as 1 even if there is a variance greater then + or - 2 cents in cells B2, C2, D2 or E2. I am not sure why this OR Statement is not recognizing to answer "ERROR" when there is a value greater then + or -2 cents.

Try this:

=if(max(abs(b2:e2))<=0.02,1,"error")

As this formula involves an array you have to hit ctrl + shift + enter for it to work.

Hi NBVC,

Thanks for your replies. When ever there is a variance in any of the cells B2, C2, D2 or E2, I would like "Error" to show up when the variance is greater then +/- 2 cents. So if the cell has a value of 3 cents or higher or - 3 cents, -4 cents...etc, "Error" is the result. If any one of the cells has a value of -0.02, -0.01, 0, 1 or 2, I need "1" to shows up.

Also thank you Down Under for your reply. I have tried your formula but got a "#VALUE!" error.

Did you remember to hit ctrl + shift + enter instead of just enter? I've tried it again on my workbook and it still works fine.

I have hit ctrl + shift + enter and the formula executed but have noticed that I need a value in cell B2 for it to work. If B2 is blank, the formula does not work.

Hmm, it works okay on mine if B2 blank. See if this works.

I see that it does work on your spreadsheet but not sure why it would not work on mine.

Did the reverse work?

Yes, I have used this formula =IF(OR(ABS(B2)>0.02,ABS(C2)>0.02,ABS(D2)>0.02,ABS(E2)>0.02),"ERROR",1) and it has worked
I appreciate all the assistance.