Excel - test 3 cells

I'd like to perform a that tests one cell, then if false investigates two other cells.

E.g. If cell 1 says yes then result = yes, if cell 1 says no then test cell 2 and cell 3; if they say yes then result = yes.

e.g. something like:

=IF(OR(H2="100%", I2="investigate" AND j2="100%"),"100%")

obviously that isn't working but hopefully someone understands my ramble.

Thanks

Solution 1:

Depending on what kind of data you are putting into your fields, I have used the following:

=IF(B3="yes","yes",IF(C3="yes",D3,"no"))

How it works is, it checks what is in the first cell, if this is true, puts a "yes", if this is false, it checks cell 2, now if cell 2 is true it puts in whatever is in cell 3 so if cell 3 is a yes, it prints yes if cell 3 is a no, it prints no.

Technically my formula doesn't compare if cell 2 & 3 are both true.

Solution 2:

Try this:

IF(H2="100%", "YES", IF(AND(I2="Investigate", J2="100%"), "YES", "NO"))

This tests H2, if the value is 100% then it will show YES if the value is anything else then it will test to see if the value of I2 is Investigate and the value of J2 is 100%. If so it will give you YES if not it will give you NO.

Note that the value 100% is a string, if you calculate the percentage then you will need to remove the speech marks and change the formatting.