Conditional formatting a cell containing "<"
 

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!

Conditional formatting a cell containing "<"
 

Try something like this:

Conditional Formatting
Formula Is:

=--SUBSTITUTE(A2,"<","")$Z2

--
Biff
Microsoft Excel MVP


"f8" wrote in message
...
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!

Conditional formatting a cell containing "<"
 

Custom Format the cells rather than precede with a typed "<"

< .0000


Gord Dibben MS Excel MVP

On Sat, 17 Oct 2009 11:24:01 -0700, f8 wrote:

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!

Conditional formatting a cell containing "<"
 

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


"f8" wrote:

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!

Conditional formatting a cell containing "<"
 

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

"B. R.Ramachandran" wrote:

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


"f8" wrote:

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!