"OR" in a conditional format for defined names

[alibongo]

Hello All,

Please can you help me to solve this problem.
I have monthly %'s, in this case m19(e.g. Jan) = 88% and L19(e.g. Feb) = 104% and $R19 is my static target of 100%. If the % is within + or - 5% of the target I want to show the defined name of either down_good or up_good.

down_good and up_good are up and down arrows in the wingdings format. The same is true of the other defined names within the formula below.

This is the formula that does this:-
=IF(ISERROR(L19),"",IF(AND(M19<L19,M19-$R19<-0.1),down_bad,IF(AND(M19>L19,M19-$R19>0.1),up_bad,IF(AND(M19<L19,M19-$R19<-0.05),down_near,IF(AND(M19>L19,M19-$R19>0.5),up_near,IF(AND(M19<L19,M19-$R19<0),down_good,IF(AND(M19>L19,M19-$R19>0),up_good)))))))

S19 = where the trend arrow are
I then use conditional formatting to say if the cell value is =OR(S19=down_bad,S19=up_bad) = font red
if =OR(S19=down_near,S19=up_near) = font orange
if =OR(S19=down_good,S19=up_good) = font green

The formula works fine but the conditional format always defaults to the first colour i.e. red and when I drag it along to compare other months it always remains the colour associated with the first conditional formatting condition.

Can anybody help please?

For my sanity I changed the defined names to text i.e. "up_bad" etc. and also in the conditional formatting and this changes as it should.

Cheers, Alistair

 

[KenWright]

Works fine for me. Assuming data is specified as per your example, then with the values you have listed the formula in cell S19 returns up_good.

After applying conditional formatting to cell S19, ensuring that you have gotten rid of the $ signs that would normally lock it to that cell, so that in effect you have added 3 conditions and you end up with

=OR(S19=up_bad,S19=down_bad)

=OR(S19=up_near,S19=down_near)

=OR(S19=up_good,S19=down_good)

And in the above scenario I get whatever format I have put in for the up_good / down_good option.

Just out of interest though, would 95% and 99% be valid possibles for L19 and M19 respectively, only in my test sheet thsoe dummy values simply returned FALSE and none of your scenarios.

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[alibongo]

Hi Ken,

I have just realised I can't do what I want using this excel formula.

As you pointed out at the bottom of your mail I have not covered all the possible scenarios, if I wanted to the nested if statement would have 12 if statements as it can be up_good twice e.g. jan 101% feb 103% and jan 97% and feb 99%.

However, I could not get the conditional formatting to work with what I had in the first place. Did you also define the name up_good etc, as oppose to just having "up_good" in the formula?

Do you have any suggestions? I am sure it can be done in VBA but I am a real beginner - chapter 2 of my first John Walkenbach book...

Any help would be appreciated,

Thanks again, Alistair

 

[KenWright]

The CF worked fine with the defined names for me.

Give us a list of all your conditions and what the result needs to be for each and we can take a look at a different type of formula.

What's that VBA thingy you referred to??? <g>

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[alibongo]

Hi Ken,

The possible scenarios are:-

Jan 101% Feb 103% Target 100% = up_good
Jan 97% Feb 99% Target 100% = up_good
Jan 103% Feb 101% Target 100% = down_good
Jan 99% Feb 97% Target 100% = down_good
Jan 107% Feb 109% Target 100% = up_near
Jan 91% Feb 93% Target 100% = up_near
Jan 109% Feb 107% Target 100% = down_near
Jan 93% Feb 91% Target 100% = down_near
Jan 111% Feb 113% Target 100% = up_bad
Jan 87% Feb 89% Target 100% = up_bad
Jan 113% Feb 111% Target 100% = down_bad
Jan 89% Feb 87% Target 100% = down_bad

Which I put into the nested if statement:-

=IF(AND(Feb>Jan,Feb-Target<-0.1),up_bad,IF(AND(Feb>Jan,Feb-Target>0.1),up_bad,IF(AND(Feb>Jan,Feb-Target<-0.05),up_near,IF(AND(Feb>Jan,Feb-Target>0.05),up_near,IF(AND(Feb>Jan,Feb-Target<0),up_good,IF(AND(Feb>Jan,Feb-Target>0),up_good))))))

and the same if for the down scenarios where Feb<Jan.

Conditional formats are the same as before.

Thanks for your help Ken.

Cheers, Alistair