Conditional formatting a cell containing "<"

  • Thread starter Thread starter f8
  • Start date Start date
F

f8

Folks,

yet another conditional formatting question:

I've got bit sheets with numbers, but as they are analytical results, there
are no "0" values, but these are displayed with a leading "<", for example
"<0.0002".

I want to format this with conditional formatting. comparing every cell in a
row with the content of a single cell at the end of that specific row. (Say,
mark all numbers > $Z$2, with "10" in $Z$2) The problem is that cell content
with that leading "<" is treated as text and not as a small number and
therefore is erroneously formatted when using the above conditional
formatting method.

Any idea how to get this sorted without changing anything in the
spreadsheet? I need the "<" to stay in place.

Cheers!
 
Try something like this:

Conditional Formatting
Formula Is:

=--SUBSTITUTE(A2,"<","")>$Z2
 
Custom Format the cells rather than precede with a typed "<"

< .0000


Gord Dibben MS Excel MVP
 
Hi,

Assuming that all the rows have the same length (for instance, they all end
at column Z),

Use the following formula for CF in Row 2.
=--MID(A2,2,99)>$Z2
and extend the CF to other columns and rows.

With regards,
B. R. Ramachandran
 
Hi B.R. and thanks for your help!

Alas, this did not work for the following reason:

All is good if the cell content is text. But if the cell content is a
number, "20" for example, your formula will return "0" which is not desirable
for me.

However, the following solution worked for me. I simply set"<x.xxx" "0" and
compare with $Z2:

=(IF(LEFT(B2)="<",0,B2))>$Z2

Cheers
 
Back
Top