Check values in 4 cells and return appropriate remarks depending on the cell value

[dmk18572]

Greetings,

Please help me creating a formula for the following requirement.

I have prepared an excel sheet with over 150 parameter taking input from phd uniformance (historian) and compared them with their respective set alarm &trip values.
I have shown four such parameters (Steam Turbine Radial Vibrations)
Accordingly I have the traffic colour codes (Green-Normal, Yellow-Alert, Red-Danger) using conditional formatting AND also return text in Column M

Next I want to compare the four outputs in column M and return back with following in column O:
1) NORMAL: if all four are "Normal"
2) ALERT: if any one of the four is "Alert", rest are "Normal"
3) DANGER: if any one of the four is "Danger" and rest are "Normal" or "Alert"

I've tried the following but it does not work
=IF(OR(M5="Danger", M6="Danger",M7="Danger",M8="Danger")="Danger", "DANGER", IF(OR(M5="Alert", M6="Alert",M7="Alert",M8="Alert")="Alert","ALERT","NORMAL"))

Regards,
dmk18572

 

[luirib]

dmk

I think you need to remove the ="Danger" for the same part of the IF statement. By having it there you make that statement a Boolean one which is not what you want. Regarding the second part, I would do it with COUNTIF. I changed below the first part and gave you the guideline for the second (you also need to get rid of the Boolean),

=IF(OR(M5="Danger", M6="Danger",M7="Danger",M8="Danger"), "DANGER", IF(COUNTIF([Your condition]),"ALERT","NORMAL"))

I hope this helps.