0

I want to search each cell in a column for a certain pharse. I figure I should use IF statements but since the following text can be anything I need to incorporate a SEARCH statement as well. Like at my sample code below.

=IF(SEARCH("up",status_value),("<div style='width:25px; height:25px; border-radius:25px; background-color:green;'></div>")
,IF(SEARCH("Down",status_value),("<div style='width:25px; height:25px; border-radius:25px; background-color:yellow;'></div>")
,IF(SEARCH("Degraded",status_value),("<div style='width:25px; height:25px; border-radius:25px; background-color:red;'></div>")
,IF(SEARCH("100-90",status_value),("<div style='width:25px; height:25px; border-radius:25px; background-color:green;'></div>")
,IF(SEARCH("89-80",status_value),("<div style='width:25px; height:25px; border-radius:25px; background-color:yellow;'></div>")
,IF(SEARCH("79-70",status_value),("<div style='width:25px; height:25px; border-radius:25px; background-color:red;'></div>")
,IF(SEARCH("<10",status_value),("<div style='width:25px; height:25px; border-radius:25px; background-color:blue;'></div>"),"")))))))

So as you see the status column corresponds to the status_values col. It allows me to have this in the calculated column but when it renders up initially as enter image description here

If I change the value in status_values then there should be a different colored circle it comes up as #Name? enter image description here

1 Answer 1

0

You could use the below formula:

=IF(ISERROR(SEARCH("up",status_value))=false,("<div style='width:25px; height:25px; border-radius:25px; background-color:green;'></div>")
,IF(ISERROR(SEARCH("Down",status_value))=false,("<div style='width:25px; height:25px; border-radius:25px; background-color:yellow;'></div>")
,IF(ISERROR(SEARCH("Degraded",status_value))=false,("<div style='width:25px; height:25px; border-radius:25px; background-color:red;'></div>")
,IF(ISERROR(SEARCH("100-90",status_value))=false,("<div style='width:25px; height:25px; border-radius:25px; background-color:green;'></div>")
,IF(ISERROR(SEARCH("89-80",status_value))=false,("<div style='width:25px; height:25px; border-radius:25px; background-color:yellow;'></div>")
,IF(ISERROR(SEARCH("79-70",status_value))=false,("<div style='width:25px; height:25px; border-radius:25px; background-color:red;'></div>")
,IF(ISERROR(SEARCH("<10",status_value))=false,("<div style='width:25px; height:25px; border-radius:25px; background-color:blue;'></div>"),"")))))))

Test result:

enter image description here

4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.