Exceptions within conditional formatting

Copper Contributor

I have an excel table listing various data formats. I applied conditional formatting to cells, based on the row, so that cells that are greater than or equal to a certain value are highlighted. My problem is that some values in the cells are entered as text (specifically ND, Non-Detect) and are automatically highlighted as part of the rule. I have tried playing around with ISNUMBER and IF functions to hopefully exclude these cells but haven't had any luck yet. Any suggestions?

3 Replies

One method would be to select the column that you want excluded, and create a new rule using the formula =ISTEXT(C3), where C3 is the first cell of your selection, and don't change any of the formatting.  Then, in the Conditional Formatting Rules Manager, ensure that it is listed first, and check the box that says Stop If True.  This formula will stop for any text, but if you want it to not highlight for the specific terms,  you could use the formula =OR(C3="ND",C3="Non-Detect")

Or that could be rule formula like

=(A1>5)*ISNUMBER(A1)

 

Probably a little late, but this is what I found to work around it.

@Megan Cooper 

 

=IF(AND(ISERROR(SEARCH("ND",D11)),D11>=D$9),TRUE,FALSE)

 

Where D11 is my cell value for a sample/analyte and D$9 is the cell listing my RSK or MCL.  A lot of frustration went in to figuring it out, so I hope it helps someone.