SOLVED

Conditional IF AND/OR

Occasional Contributor

Hi,

 

I am working on a spreadsheet and I have a formula that I want to add in a conditional situation.

 

This is what I have:

 

=IF(OR(K12="",L12=""),"",IF(AND(K12="A",L12>=50,L12<=250),"Yes","No"))

 

I want to add in that if there is a particular value in M12 that my result will be No. However, I still want a result even if there is nothing in M12.

 

Situation is: We check sterility on new lots of media but we perform QC weekly regardless. I would like to have a response of N (for sterile) to make my end result be No. But when the area is blank (when it isn't a new lot) I would still like it to give me a result. I'm attaching an example of what I mean since I don't think this is too clear. Thanks!

3 Replies

@Sharon_Dquest1 

 

May I make a request: put some data--it can be dummy data if need be--into a few of the rows of this sample spreadsheet.

In the absence of any representative data, it's hard (for me, at any rate) to do a realistic appraisal of what you've written, why it might work or not....

best response confirmed by Hans Vogelaar (MVP)
Solution

@Sharon_Dquest1 

=IF(M12="N","No",

IF(OR(K12="",L12=""),"",

IF(AND(K12="A",L12>=50,L12<=250),"Yes","No")))

 

If i correctly understand what you want to do, you can apply above formula. I changed data validation for cell M12 from Y/N to Y;N in order to select either "Y" or "N".

Perfect! Thank you so much!