Conditional Formatting -- Ignore "less than" (<) sign

[Tryintouseexcel]

I am trying to set up conditional formatting to flag all cells below 0.5. I am finding that cells with a less than (<) sign get flagged even if they are less than 0.5. For example when I set up conditional formatting to flag everything below 0.5 a cell with the value <0.0030 will be flagged.

How do I make excel ignore the less than sign when doing conditional formatting.

Thanks

 

[Fluff]

What rule are you using?

 

[Tryintouseexcel]

Conditional Formatting -- highlight cells rule -- greater than

 

[Tryintouseexcel]

I misspoke in my first post. I am trying to get excel to flag values that are greater than 0.5. But excel is flagging values that are less than 0.5 if they have a < sign. For example the value, <0.0030 would be flagged even though it is not greater than 0.5.

How do I make excel ignore the < sign?

 

[Fluff]

How about
=AND(ISNUMBER(J2),J2>0.5)

 

[Tryintouseexcel]

That didn't work. I may not have applied that formula correctly...

There is an option to use the conditional formatting>highlight cells rule>greater than, and then go to manage rules and add a new rule... But I don't know.

Here is a screen shot. Excel flagged the Cyanide value of <0.0030 even though <0.0030 is not greater than 0.5. How do I make excel ignore the < sign? Thanks.

 

[Fluff]

You need to select New Rule, Use a formula & then enter the formula.

 

[Tryintouseexcel]

OK, it worked for that cell. Thank you.

I have a whole bunch of cells I need to apply this or a similar conditional formatting to. Is there a varient that would allow me do this without inputting this formula on every single cell?

I applying the formula to multiple cells with this modification ( =AND(ISNUMBER(B2,B12,B22,B32),B2,B12,B22,B32>0.5) It did not work... I need to do this for cells representing 4 sampling events per year at 7 different sampling locations. I am hoping for something that will be a little faster. If it's not to much trouble...

 

[Fluff]

If you just want it to look at the Cyanide values, select B2 to last row in B & then use this formula
=AND(LEFT(A2,7)="Cyanide",ISNUMBER(B2),B2>0.5)

 

[Tryintouseexcel]

Awesome. Thanks so much. That did it.