Evaluate values and display appropriate text

[chloec]

Here's another item I have been struggling with:


1. Field number 1 has this formula:

=IF(I53="","N/A",IF(I53*100<3.49,"GOOD",IF(I53*100<5.5,"ALERT","UNSATISFACTORY")))


2. Field number 2 has this formula:

=IF(I54="","N/A",IF(I54*100<74.49,"UNSATISFACTORY",IF(I54*100>89.5,"GOOD","ALERT")))


Note the formulas above are inverted if you compare them!


3. Field Number 3 needs to evaluate fields 1 + 2. If "UNSATISFACTORY" appears in either cell, formula must display "Unsatisfactory". If it doesn't appear, then formula must check to see if "ALERT" appears in either cell. If Alert doesn't appear, then display "good". If Good doesn't appear, then write "N/A", I assume.


Can you kindly help me with this difficult formula?

 

[SirJB7]

Hi, chloec!


Try this:

=IF(AND(A2="UNSATISFACTORY",B2="UNSATISFACTORY"),"UNSATISFACTORY",IF(AND(A2="ALERT",B2="ALERT"),"ALERT",IF(AND(A2="GOOD",B2="GOOD"),"GOOD","N/A")))


Asumming A2 is your field 1, B2 is your field 2, and that you tried to mean that if both UnSat then UnSat, if not, if both Alt then Alt, if not, if both Good then Good, if not, N/A.


If you didn't want to mean that, please clarify what you posted before at point 3. Thanks.


Regards!

 

[chloec]

Hi There,

Thanks for the quick reply!

I didn't mean, "if both". I need to formula to prioritize text. If Unsatisfactory EVER appears..display it over any other text. If ALERT apppears, and Unsatisfactory does not. Show Alert. If Neither Unsat NOR Alert appears, show GOOD.


Example:

[A2 + B2 = FORMULA DISPLAY FIELD]

UNSAT + UNSAT = Display UNSAT

UNSAT + ALERT = Display UNSAT

UNSAT + GOOD = Display UNSAT


....and so on.


GOOD + GOOD = Display GOOD

GOOD + UNSAT = Display UNSAT

ALERT + GOOD = Display ALERT

 

[SirJB7]

Hi, chloec!

Just replace each occurrence of AND by OR in my formula.

Regards!

 

[chloec]

Thanks for the help SirJB7! I struggled with that for so long, but the solution is really simple!

 

[Luke M]

Just one more way to "skin the cat":

=IF(COUNTIF(A2:B2,"UNSAT"),"UNSAT",IF(COUNTIF(A2:B2,"ALERT"),"ALERT","GOOD"))

 

[SirJB7]

Hi, chloec!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!