2
I am having a real tough time here writing a conditional formatting formula for my spreadsheet.
I have a table of concentrations from a lab analysis, and various results have come back as below a detection limit (e.g. result returned as "<0.0001"). I want a conditional formatting rule to identify all results where the detection limit is higher than the specific threshold value I am comparing the results against (e.g. E14).
What I need the formatting rule/formula to do is ask these two questions, and only format if both are answered YES/TRUE:
Does M14 contain a less than symbol "<"?
(ignoring the less than value "<") is M14 value more than E14
(an example in the screenshot would be N17 where the rule should answer both queries with YES, and therefore should format the cell)
Hopefully for a bit of clarity, I've included a screenshot. I would have though this could be solved with an AND and SUBSTITUTE function, but I cannot seem to get it to work.
This is what I have tried so far:
=((SUBSTITUTE(M14,"<",""))AND(14>=E14,M14=""<*""))
Any help on how I can iron out this formatting formula would be so great. The irritating thing is, I have done this before about 2 years ago successfully, and now can't remember (or find the original sheet) how to do it.