IF(AND(x,y,z),yes,"") to the next step

[SuperSpartan1431]

I am going to try and simplify down what I am doing to see if anyone can help. If I need to get into more detail, I can basically send the file to someone. TIA for any help.

I have 3 cells that are blank. G2, H2 and I2

2 rows below the cells I have a cell in which I have the formula below.

IF(AND(G2=x,H2=y,I2=z),yes,"")

This displays "yes" if cell G2 has value x, H2 has value y and I2 has value z. It displays "" if any of those are not true.

What would the formula be if I wanted the above to work if one of the three cells is correct and the others are not filled in yet or "".

So it should still return "yes" when G2=x, but H2 and I2 are "", but not return "yes" if G2 does not equal x and H2 and I2 are "".

 

[njimack]

Replace AND with OR.

 

[SuperSpartan1431]

Replace AND with OR.

I simplified this too much. x,y and z are actually ranges. If I use Or it will always be "yes" because x in actuality is <=G2+5 and >=G2-5.

 

[AlphaFrog]

=IF(OR( 
       AND(G2-5<=x, x<=G2+5), 
       AND(H2-5<=y, y<=H2+5), 
       AND(I2-5<=z, z<=I2+5)), 
                              "YES","")

This will return Yes if one of these three are true...
x is within 5 of G2
y is within 5 of H2
z is within 5 of I2

 

[njimack]

I simplified this too much. x,y and z are actually ranges. If I use Or it will always be "yes" because x in actuality is <=G2+5 and >=G2-5.

Use a combination of OR and COUNTIF. If you post your actual data and desired output, you're more likely to get a solution.