Problem with Excel 2010 conditional formatting

[percy83]

Hi all,

Iäve got some trouble with the new Excel 2010 conditional formatting.

I've got these three rules applied:

less then 0 = green
between 1-14 = yellow
above 15 = red

The problem that I am having is that with this 2010 version (used to run 2003) is that "rule green" is applied when a cell is blank or containing text values.

Is there something new with the logic in Excel 2010 or am I just being stupid?

Thanks!

 

[Aladin Akyurek]

Hi all,

Iäve got some trouble with the new Excel 2010 conditional formatting.

I've got these three rules applied:

less then 0 = green
between 1-14 = yellow
above 15 = red

The problem that I am having is that with this 2010 version (used to run 2003) is that "rule green" is applied when a cell is blank or containing text values.

Is there something new with the logic in Excel 2010 or am I just being stupid?

Thanks!

Try...

=ISNUMBER($A2) * ($A2 < 0)

for green. If you don't want to exclude 0 itself, change < to <=.

=($A2 >= 1) * ($A2 <= 14)

for yellow.

=ISNUMBER($A2) * ($A2 >= 15)

for red.

I expect 2003 and 2010 not to behave differently.

 

[Derek Brown]

If you have an hour to spare, you may fnd Mike Girvin's ExcelIsFun podcast of interest:
http://www.youtube.com/watch?v=LOaezdoyJDE&playnext=1&list=PL2ACA26D4485F8EDD
You can of course also use AND in the rule:
=AND($A2 >= 1,$A2 <= 14)