Problems with Conditional Formatting

[Niall]

Hello All,

This may have been covered by previous topics, but I could not see it.

I want to apply 3 different conditional formats to a column where the
cells have a formula applied as follows:

=IF(Q11>=5,"1",IF(Q11>=4,"2",
IF(Q11>=3,"3",IF(Q11>=2,"4",IF(Q11<2,"5"))))), Q being a column where a
SUM is calculated.

The conditions I want are RED for value >=5, ("1"); ORANGE for value

=4, ("2"); and YELLOW for value >=3, ("3").

When I apply the conditional formatting it only appears to work where I
use 'Formula is'. And then it will only apply one condition, i.e. the
higher value. I have tried using 'Cell value is' and putting in both
the presented value, i.e. within the quote marks of the IF argument,
and the calcualted value, i.e. the value range from the SUM column.

Any ideas would be greatly appreciated

Regards,

Niall

 

[Gord Dibben]

Works for me with Cell Value is: equal to ="1" for condition1

="2" for condition2

="3" for condition3

Watch out for Excel placing more double quotes around the parameter.

i.e. ="""1"""

Delete the extras if Excel interferes.

Also tried with using greater than or equal to 5 and 4 and 3 as conditions1
through 3

Worked fine either way


Gord Dibben MS Excel MVP

 

[Ian]

Condition 1 should be Cell value is greater than or equal to 5, condition 2
the same but 4, and condition 3 the same but with 3. This works for me by
entering discrete values in the cell.

Looking at your formula, the results of your IF conditions are being entered
as text because you have put them in quotes.
Try
=IF(Q11>=5,1,IF(Q11>=4,2,IF(Q11>=3,3,IF(Q11>=2,4,IF(Q11<2,5)))))

 

[Niall]

Many thanks - this worked for me

Niall

Works for me with Cell Value is: equal to ="1" for condition1

="2" for condition2

="3" for condition3

Watch out for Excel placing more double quotes around the parameter.

i.e. ="""1"""

Delete the extras if Excel interferes.

Also tried with using greater than or equal to 5 and 4 and 3 as conditions1
through 3

Worked fine either way


Gord Dibben MS Excel MVP

 

[Niall]

Ian,

Removing the quotes worked within the IF argument.

Many thanks,

 

[Niall]

Gord,

Having succeeded at that I then went & downloaded the CFPlus package
from Frank Kabel & Bob Philips
,(http://www.xldynamic.com/source/xld.CFPlus.Download.html ). This
worked initially, but now I can only get it to work for specific
functions, i.e. one condition only). The function I am using is:

=IF(H11>=5,"Priority 1",IF(H11>=4,"Priority 2", IF(H11>3,"Priority
3",IF(H11>=2,"Priority 4",IF(H11<2,"Priority 5"))))), with a view to
ascribing RED, ORANGE, YELLOW, GREEN & BLUE backgrounds to the cells.
There doesn't seem to be an issue with double " marks, as it will work
for a condition. I got it work yesterday, but no joy at all to day.

Any views would be appreciated.

Regards,

Niall