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]

Look at the ABS function ... that will help you.

 

[outtahere]

Thanks, will try

 

[outtahere]

Hi, I looked this up but was not sure how it would help. I need to have negative figures as the cells need to be formatted (painted) when a change in value exceeds a certain threshold (negative or positive). Does that make sense?

 

[GlennUK]

If you ABS any calc then you can test against a positive threshold no matter whether the actual result of the calc is positive or negative.

 

[outtahere]

Can you help me understand this? If I have seven thresholds -5,-3,-1,0,1,3,5. Do I put =ABS in front of all my formulas and if so how does it distinguish between -5 and +5 and paint them different colours?

 

[GlennUK]

Sorry I thought you wanted +/- 1 to be one colour +/- 3 to be another, and +/- 5 to be another.

When you say:

for example =(D4/C4)-1<=-3% and same formula for -5% it only picks up anything less than zero

... -5% is also less than -3% ... if you are ticking "Stop If True" in your conditions then that may cause problems.

 

[outtahere]

I did not tick stop if true. I assumed it would calculate in a hierarchy. Do I then need to make each condition something like =and((D4/C4)-1)<=0,>=-1?

 

[GlennUK]

Why don't you list your condition formulas ( and what format you are applying )?

 

[outtahere]

OK here we go:

condition 1: if ((B1/A1)-1)<=A32 (-5%) format (black with white text
Condition 2: if ((B1/A1)-1)<=A33 (-3%)format brown with white text
condition 3: if ((B1/A1)-1)<=A34 (-1%) format red with white text
condition 4: if ((B1/A1)-1)<= A35(0%) format orange with white text)
condition 5: if ((B1/A1)-1)>= A36(0%) format white with black text
and so on until I reach =5%. note that I use the phrase < (less than) with negative numbers when describing a greater negative figure. Is that correct? Once I get this formula right then I need to format the whole table so that each month as I update the sheet with new figures, the previous column is compared. Does that explain the issue better or worse?