Conditional Formatting with 3 options

  • Thread starter Thread starter Loxley
  • Start date Start date
L

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
 
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.
 
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
 
=this_month_ytd<last_month_ytd

irrelevant whether they are negative or positive
 
Back
Top