multiple conditional formatting

[outtahere]

I have searched the database for a similar problem but cant find one.

Threadneedle Lat Am 2531 2300 2695 2620
MFM Slater Growth 2500 2583 2626 2673
JPM Natural Resources 2541 2819 2761 2756
First State Indian Sub 2528 2577 2548 2447
Blackrock European Dyn 2550 2742 2702 2696
Smith William Gold and Resources 2550 2785 2655 2632
SL Inv UK Smaller Cos 2524 2739 2764 2772
Ecclesiastical Higher Income 2505 2535 2489 2475

Each column is supposed to be in line but I do not have software to copy the sheet yet. imagine four columns. Each time I add a new column I would like to colour the latest cells according to % change (figures range from -5% to +5%) e.g. -5%, -3%, -1%, 0% etc. I have tried it and got it to work to a degree but when I use negatives. for example =(D4/C4)-1<=-3% and same formula for -5% it only picks up anything less than zero.

 

[GlennUK]

Conditions 1,2,3,4 need to have their orders reversed.

 

[outtahere]

Hi, there did you receive this last message on the 8th?

 

[GlennUK]

Yeah, if you mean, did I read your posting in this thread on the 8th ... which is why I posted the solution.

 

[outtahere]

OK sorry I did not see the second page. If I reverse the negatives, putting lowest negative first then it would look like this right?

Condition 1 relates to +5
Condition 2 +3
Condition 3 +1
Condition 4 0
Condition 5 -1
Condition 6 -3
Condition 7 -5

is that how you meant?

 

[GlennUK]

Well, yeah, except now you've altered the order of the positive ones.

 

[outtahere]

Hmm, I see, so how would you suggest I order both positives and negatives?

 

[GlennUK]

condition 1: if ((B1/A1)-1)<= A35(0%) format orange with white text
condition 2: if ((B1/A1)-1)<=A34 (-1%) format red with white text
Condition 3: if ((B1/A1)-1)<=A33 (-3%)format brown with white text
condition 4: if ((B1/A1)-1)<=A32 (-5%) format (black with white text
condition 5: if ((B1/A1)-1)>= A36(0%) format white with black text
... the rest as before.

 

[outtahere]

Hi there, I cant think what I am doing wrong. The order I put in was ,<=0,<=-1,<=-3,<=-5,>=0,>=1,>=3,>=5. The problem is as before where it will pick the lowest figure. If I have a figure that is about -3% lower than the previous one it shades it as per the -5 figure. I am stuck for ideas

 

[GlennUK]

I will send you a Private Message with my email address ... all should be working according now, and I need to see what is happening, if you can send me the actual book.

 

[outtahere]

I will send you a Private Message with my email address ... all should be working according now, and I need to see what is happening, if you can send me the actual book.

Very kind of you