Conditional Formatting with 3 options

[Loxley]

Hi, i would like to know if anyone has successfully created
conditional format with 3 options whereby 3 colours are displaye
depending on the cell/formula result contents. I would say i have ver
strong excel skills and although i could easily program this in VBA th
spreadsheet needs to work without VBA in this instance.

basically my problem is to monitor changes in performance between
dates for a budget whereby a positive improvement of actual vs budge
is one colour a negative performance is another colour ( now that wa
the easy bit ) the difficult bit is to show a third colour when th
variance value being coloured is positive but is still less than the
year to date value.

for example

Plan May ytd = 70
Actual May ytd = 39
Variance ytd = (31)


Plan Feb ytd = 43
Actual Feb ytd = 9
variance ytd= (34)

therefore there is a +3 improvement in the ytd variance, but overal
the variance is still negative.

it is this condition which i need to trap.

it would be easy to use more than one column but it all needs to b
displayed in just one with 3 colours indicating the condition
effectively there are 4 with 'blank' background being a positive o
static change in performance and ytd is still positive )

at the moment the conditional formating formula is quite simple for th
+/- state with it just being coloured with a if greater than zero o
less than zero option.

thanks
Mar

 

[Bob Phillips]

You need to get the priority order correct and it sounds here that you
should put your current 3rd condition in 2nd and vice versa.

 

[Loxley]

I appreciate your help but your reply doesnt actually tell me anything.

What stumps me is the actual formula to enter in the conditiona
formating dialog box.

at present i have

condition1
cell value is less than 0 then colour red

condition2
cell value is greater than 0 then colour green

simple so far, but what i want to do is then evaluate from a 'third
cell that if that third cell is negative to apply another colour i
blue.
but what formula ?
the thing is the 'cell' already contains a formula which is a looku
and i can apply a logic statement to that cell to determine its 'state
but what i need in the cell is the actual number

 

[Bob Phillips]

=this_month_ytd<last_month_ytd

irrelevant whether they are negative or positive