Conditional formatting

[andycuk7]

Hello,

I have a conditional format that turns a cell either green or red depending if a criteria is met. However, the conditionan format is either above one number or below one number. I would like to add an amber range e.g tgt number +/-5 is green, +/- 5-10 is amber and +/- 10+ is red.

Below is the formatting i have in at the moment i was just wondering if anyone knew how to put the formatting for the amber requirments please ??

Thanks

Andy

Green formula is

=(ROUNDDOWN(C22-C21,6)<=0.003472)

red formula is

=(ROUNDDOWN(C22-C21,6)>=0.003472)

Sorry, the +/- 5 ect are mins hence the 0.003472 = 5 mins

[etaf]

so 10 Mins is 0.006944

Green formula is

=(ROUNDDOWN(C22-C21,6)<=0.003472)

red formula is

=(ROUNDDOWN(C22-C21,6)>=0.006944)

amber , is less than 10mins and greater than 5 mins

=AND( (ROUNDDOWN(C22-C21,6)<0.006944), (ROUNDDOWN(C22-C21,6)>0.003472))

[andycuk7]

so 10 Mins is 0.006944

Green formula is

=(ROUNDDOWN(C22-C21,6)<=0.003472)

red formula is

=(ROUNDDOWN(C22-C21,6)>=0.006944)

amber , is less than 10mins and greater than 5 mins

=AND( (ROUNDDOWN(C22-C21,6)<0.006944), (ROUNDDOWN(C22-C21,6)>0.003472))

Thanks for that, however I now only get green cells ??...should there be spaces in the amber formula??..i've tried removing them just in case but no chenge ??

Andy

Atached is the sheet in question

[etaf]

the green cells is because a blank cell will be seen as zero and so meets the condition
=(ROUNDDOWN(C22-C21,6)<=0.003472)

you are just comparing B7 and B6
does that apply to all the cells or did you want to change the comparison for each cell ?

to apply to all the cells use $ values for
B7 and B6

$B$7-$B$6

see attached - updated to include amber and applied to all cells for condition in B6 and B7 only

explain exactly how you want to apply to the cells - changing the $ and also the applies to range will change how the format works