Applying formula to conditional formatting in Excel

Question

I use an Excel table to store concentrations of substances. If the concentration is less than the limit, a value of "ND" or "LOD" is logged to the Excel table.

For further use, I want the cells with values "ND" and "LOD" to show "<[limit]" without affecting the actual value in the cell ("ND" or "LOD"), and without adding additional columns/cells to the Excel.

How the table looks like:

	Concentration
Limit	5
	25
	ND
	18
	LOD

How the table should look like:

	Concentration
Limit	5
	25
	<5
	18
	<5

I have succesfully gotten the formula =IF(OR(B3="ND";B3="LOD");"<"&B2;B3) to work in a a different cell. I have unsuccessfully tried to apply the same formula to conditional formatting.

How to achieve the wanted result in Excel?

Answer 1

Your formula is fine, but just need the 'OR' part of it:

=OR(B3="ND";B3="LOD")

Then try a custom format of

;;;"<5"

in conditional formatting.