CF with 4 conditions

[sahafi]

I need to have a conditianal formatting applied to my data as follow:
I would like to compare the condition to a value that changes, say every
month. On $A$1 I have my value 106.2%.

Condition1 when the value is between A1 and A1 + 0.05 = Yellow
Condition2 when the value is between A1 and A1 - 0.05 = Green
Condition3 when the value is >= (A1 + 0.05) = Red
Condition4 when the value is between (A1 - 0.05) and 0.001 = Red
Otherwise no format (< 0.0001). Leave zero values without formatting.

I managed to get the first 3 conditions to work but not the last one as
Excel will allow only 3 condition plus the blank one. I have tried to add
additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I
ended up with the same result. I have also tried the CFPlus Add in, but it
didn't work.
Any help is greatly appreciated.

Thanks.

 

[David Biddulph]

If you use "Formula is", rather than "Cell value is", then you can use an
OR() condition.

 

[Elkar]

Its a common misconception that Conditional Formatting is limited to 3
conditions. Actually, it is limited to 3 formats (4 if you count the
default), not conditions. With the use of AND and OR functions, you can have
virtually unlimited conditions.

For your third condition (red), try something like this:

=OR(B1>A1+.05,AND(B1<A1-.05,B1>.001))

HTH,
Elkar

 

[sahafi]

While your formula worked with the red formatting, the other 2 formatting are
not working as I would like them to. Here's all the formulas:
B4 is the value i'm basing my conditions on.
J7 is the beginning of my range.
#1: =OR(J7>$B$4+0.05,AND(J7<$B$4-0.05,J7>0.001)) gives 'Red', it's working.
#2: =OR(J7<=$B$4,AND(J7>=$B$4-0.05,J7>0.001)) supposed to show 'Green'.
#3: =OR(J7>$B$4,AND(J7<=$B$4+0.05,J7>0.001)) supposed to show 'Yellow'.
I'm only getting 2 format out of it. Either red and green, or red and yellow.
I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it
didn't work either.
#2: =AND(J7=$B$4,J7>=$B$4-0.05,J7>0.001)
#3: =AND(J7>$B$4,J7<=$B$4+0.05,J7>0.001)
I'm not sure if it's the order of the conditions, or I missed something.

Thanks.

 

[Tom Hutchins]

Your conditional formatting works (I recreated it using the first versions of
#2 and #3 below).

J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than
0.001

J7 is also yellow if it is zero and B4 is between zero and -.05, not
inclusive.

Hope this helps,

Hutch

 

[sahafi]

Thanks for the reply. But those are not my conditions.

Basically, if $B$4 is 106% I would like my conditions to be formatted as:

When it's equal to B4 and between B4 and B4 minus 5% then show 'green'.
When it's greater than B4 but less than B4 plus 5% then show 'Yellow'.
When it's greater than (B4+5%) show 'Red'
When it's less than (B4-5%), but greater than zero also show 'Red'.
If it's zero do not format (no background fill).

I hope I made it clearer this time.

Thanks.


-- If u change the way u look @ things, the things u look at change.

 

[Elkar]

Try these for your formulas:

Red:
=OR(J7>$B$4+0.05,AND(J7<$B$4-0.05,J7>0.001))

Yellow:
=AND(J7>$B$4,J7<=$B$4+0.05)

Green:
=AND(J7<=$B$4,J7>=$B$4-0.05)

HTH,
Elkar

 

[sahafi]

While the 'Red' and 'Green' formatting are executed correctly, the yellow
format has been ignored. The logic looks OK, but it seems to me the issue is
how to order them correctly. I have tried that, but didn't work for me. May
be someone could take a look at the formulas and see what's missing.

Thanks.

 

[Elkar]

There is no overlap among the formulas, so the order shouldn't matter. As
far as I can tell, they work they way they're supposed to. Unless I'm
misunderstanding your criteria. When I try it, I get the following results:

When B4 contains the value 100, I input the following values into J7 with
the following results:

99.94 = RED
99.95 = GREEN
100.00 = GREEN
100.01 = YELLOW
100.05 = YELLOW
100.06 = RED

Is that not right?

HTH,
Elkar

 

[sahafi]

My numbers formatted as %.
Using your example, if B4 = 100%
then the values on my range will be formatted as follow:

Green <=100% and >=95%
Yellow >100% and <=105%
Red >105% || Red <95% and >0.001%
If <=0.001 (zero) then do not format.

Hope it's clear now.

Thanks.

 

[Elkar]

It still works for me, formatted as percent or not. As long as both cells
are formatted the same way.

If, as you mentioned, the Green and Red conditions are working, and only the
Yellow is wrong, then take a closer look at the formulas. Make sure you
typed them exactly (or better yet, copy and paste from this post), and didn't
leave anything out.

If still not working, then what is happening when the condition should be
yellow? Is red or green filling in? Or is it blank? What happens if you
delete the Red and Green conditions, leaving only the Yellow?

HTH,
Elkar

 

[sahafi]

I have copied the formulas you have provided, and still no yellow formatting.
The values that are supposed to be highlighted yellow, they get formatted as
green as well as the ones supposed to be green. I have removed the 'Green'
condition, but still no yellow. Then I deleted both conditions (red and
green), and still no yellow!!. I don't know why it's working for you but not
for me.

Thanks.

 

[Elkar]

Hmm.... I'm running out of ideas here. Is the data in B4 or J7 the result of
a formula? If so, perhaps try manually typing data over these formulas just
to see what happens?

Sometimes, due to cell formatting, the value returned by a formula, and the
value displayed in the cell aren't the same. The conditional formatting
bases its results off of the actual value of a cell, not the displayed value
of a cell.

The only other thing I can think of is maybe an issue with your color
settings. Try changing the Yellow color to Blue or something else and see
what happens.

HTH,
Elkar

 

[sahafi]

OK, it's working now. It turn out that my lookup cell (B4) was of a text data
type instead of percentage! I don't know why I didn't check that. It's my
fault. But thank you for being patient, and thaks for the help.