Conditional Formatting

[ziad alsayed]

dear all

need to apply conditional formatting to the below data

if data in column F<0 to color in red
if data in column F >0 and<=5% to color in green
if data in column F >=5% and<10% to color in blue
if data in colomn F >=10% to color in pink

the problem i am facing is that some data Show #Value so i used iferror to eliminate them. when applying condotinal to such cells let say >10% ,they will be colored though they are empty.

appreciate any help

Excel Workbook
	D	E	F
13	1 640 000	1 748 000	6.59%
14	1 950 000	2 088 000	7.08%
15	2 200 000	2 349 000	6.77%
16	2 570 000	2 734 000	6.38%
17	No Range	No Range
18	3 270 000	3 531 000	7.98%
19	4 100 000	3 875 000	-5.49%
20	4 500 000	4 758 000	5.73%
FGW ref

Excel 2010

Cell Formulas
Range	Formula
F13	=IFERROR((E13-D13)/D13,"")
F14	=IFERROR((E14-D14)/D14,"")
F15	=IFERROR((E15-D15)/D15,"")
F16	=IFERROR((E16-D16)/D16,"")
F17	=IFERROR((E17-D17)/D17,"")
F18	=IFERROR((E18-D18)/D18,"")
F19	=IFERROR((E19-D19)/D19,"")
F20	=IFERROR((E20-D20)/D20,"")

 

[Tom Urtis]

Would it help if your first formula rule is

=ERROR.TYPE(F1)=3

3 is the #VALUE! error constant and I assume F1 is the first cell in column F that you are evaluating, further assuming with all these conditions you are using version 2007 or 2010.

 

[ziad alsayed]

dear Tom

i didn't get your point, please can you explain what should do?

 

[Tom Urtis]

I assumed when you wrote this:

if data in column F <0 to color in red
if data in column F >0 and <=5% to color in green
if data in column F >=5% and <10% to color in blue
if data in colomn F >=10% to color in pink

that it represented conditional formatting rules you have already established as part of conditional formatting for those cells. My suggestion was to have your first rule be, in your syntax

if data in column F is a #VALUE! error then no color

and the actual formula for that rule is what I posted:
=ERROR.TYPE(F1)=3

which, just like with any formula rule such as you already have, you'd assign a pattern color to it as you did the 4 others, but in this case the color is no color when that error condition is met.

 

[ziad alsayed]

dear tom

thanks for the explanation, i tried it and didn't work, i put as the first condition and add the others.

maybe it didn't work because of the iferror that is making the cell to show empty.

please advise.

 

[T. Valko]

dear all

need to apply conditional formatting to the below data

if data in column F<0 to color in red
if data in column F >0 and<=5% to color in green
if data in column F >=5% and<10% to color in blue
if data in colomn F >=10% to color in pink

the problem i am facing is that some data Show #Value so i used iferror to eliminate them.

Excel Workbook
	D	E	F
13	1 640 000	1 748 000	6.59%
14	1 950 000	2 088 000	7.08%
15	2 200 000	2 349 000	6.77%
16	2 570 000	2 734 000	6.38%
17	No Range	No Range
18	3 270 000	3 531 000	7.98%
19	4 100 000	3 875 000	-5.49%
20	4 500 000	4 758 000	5.73%
when applying condotinal to such cells let say >10% ,they will be colored though they are empty.

#VALUE!

Excel 2010

Cell Formulas
Range	Formula
F13	=IFERROR((E13-D13)/D13,"")
F14	=IFERROR((E14-D14)/D14,"")
F15	=IFERROR((E15-D15)/D15,"")
F16	=IFERROR((E16-D16)/D16,"")
F17	=IFERROR((E17-D17)/D17,"")
F18	=IFERROR((E18-D18)/D18,"")
F19	=IFERROR((E19-D19)/D19,"")
F20	=IFERROR((E20-D20)/D20,"")

For that condition use something like this...

=AND(COUNT(F13),F13>=0.1)

On a side note...

You're not accounting for values that are equal to 0.

You test for less than and greater than but not equal to.

 

[ziad alsayed]

thanks Biff, this worked perfectly.
thanks also for the side note

 

[T. Valko]

thanks Biff, this worked perfectly.
thanks also for the side note

You're welcome. Thanks for the feedback!