Only return text if several conditions are met

[IFSSCref]

Hey all,

I've been struggling with this formula for a while now. Hoping someone has a quick answer as to what I am doing wrong. I need the following conditions to return either "Pass", "Fail" or leave blank if a specific cell is blank. I will attach the file and try to explain what I am going for:

I need cell BM6 to return a "Fail" if cell H17 has an "X", AND if ANY of the following conditions are also met: Cell W37 is less than 5, Cell W42 is less than 1, and Cell W47 is less than 2.
I need cell BM6 to return a "Pass" if cell H17 has an "X", AND if ALL of the following conditions are also met: Cell W37 is greater than 5, Cell W42 is greater than 1, and Cell W47 is greater than 2.

Currently, anytime the H17 cell is marked with "X" it returns a pass, and no "X" returns a "Fail". Not exactly what I am going for. Basically I only need it to return pass or fail if H17 has an "X" and the rest of the formula applies.

The last thing I need from this is if H17 is BLANK, I need BM6 to stay blank. Below is the formula I have, but am attaching the file for clarification. Thank you in advance!

Please Login or Register  to view this content.

[Bob Phillips]

Is this what you want?

=IF(H17="x",IF(OR(C37<5,C42<1,C47<2),"FAIL",IF(OR(C37>5,C42>1,C47>2),"PASS","")),"")

[Bo_Ry]

Maybe try
=IF(H17="x",IF(AND(W37>5,W42>1,W47>2),"Pass","Fail"),"")

[IFSSCref]

Perfect, thank you! Much prettier than my sloppy formula!

[IFSSCref]

@Bob Phillips - Actually, I though this did the trick, but if H17 is marked "X" and nothing else has a value, it returns "fail". Can this be modified to return a "Blank" of Only H17 has "X". Basically H17 will stay marked at all times, but the other values will change.

[JeteMc]

Since H17 will stay marked it would seem that the following should be sufficient: =IF(AND(W37="",W42="",W47=""),"",IF(AND(W37>5,W42>1,W47>2),"Pass","Fail"))
Let us know if you have any questions.

[IFSSCref]

With some modification and trial and error I got it to work with bits a pieces from everyones formula suggestions. Thank you all for your help.

[JeteMc]

You're Welcome. Thank You for marking the thread as 'Solved'. I hope that you have a blessed day.