Complex conditional formatting

[joeafro84]

I know about the basics of boolean conditional formatting, ie. x<y,
x>y etc. However, I want to be able to say that for example:

If x>y colour yellow, but if x>y by more than 10% then colour red, so
that I can see which of my results requires priority. Is there a way
to do this using conditional formatting?

I am using all three conditions as well, I have a traffic light system
in effect, if x>y colour green, if x=y colour amber, if x<y colour
green.

thanks.

 

[Guest]

CF1:

=AND(NOT(ISBLANK($A1)),NOT(ISBLANK($B1)),$A1=$B1)....amber

CF2:

=$B1>$A1*1.1 .... red

CF3:

=OR($B1>$B1,$B1<$B1) ... green

You may want to add the ISBLANK tests to CF2 and CF3

HTH

 

[joeafro84]

That just leaves white cells in the cells which are less than 10%
larger than the previous cell. What I want to achieve is, all the
cells that are greater than to be red, then the cells that are more
than 10% greater than to have something else happen, for example a
different colour font. I know this is not what I originally proposed,
but I realised that this is what I need.

 

[Guest]

With CF you only have 3 colours (4 if you include white). From my previous
reply, you should be able to work out the CFs.

e.g change CF3:

=IF($B1>$A1) ...... (say) blue