summarizing conditional formats

[raking08]

Is there a way to assign a specific value to a "stoplight conditions (
without having to write a complicated "if" statement in order to summarize a
group of individual rules into a single "stoplight" when the actual data does
not normalize easily. ?

in other words, is there something behind the scenes in conditional
formating that "knows" that a cell is red, yellow or green that can be
combined into in new summary rules ?

I am rolling up a 100 query dashboard into small groups

 

[Joel]

You need a complcated macro to determine the colors that are being displyed
from a conditional formated cell. See webpage below for more detailed
explanation and a sample of the code you would need.

http://www.cpearson.com/Excel/CFColors.htm

 

[raking08]

Thanks Joel, This looks like the right approach, I was only hoping for a non
VBA solution as I am not a programmer. The active condition is exactly what I
need ( not the trick is to get it to work to return a sum for a stack of
cells..

 

[Joel]

I don't think counting the colors in the conditional formula cells is the
correct way of attacking the problem. The conditions that color the cells
can be duplicate using formulas in an auxilary cell.

I fyou have three conditions in the contional format

Red : A1 less than 25
blue : A1 25 to 50
Green: A1 50 to 100


You can put in another cell

=LOOKUP(A1,{0,25,50;"Red","Blue","Green"})

then you can count the colors in a different column.

 

[raking08]

Indeed this is a great workaround for when the stoplights are set based on
numbers and may be the way we end up as counting colours and applying a
weighting ( a second set of columns) gets me to the rolled up stoplight. I
will only need to reconfigure the individual lits base don numbers rather
than % and other formula as they are currently done. The ActiveCondition is
more elegant, but above my VBA capability...Thanks for this hint I am
applying it now.

 

[raking08]

Joey,
This was definitely the right direction! many thanks. Ultimately it took an
extra 3 columns per each individual query/metric period in order to 1st re
quantify the query metric as a color, then add the weight then vlookup to
provide a numeric value for the color and then to apply the weighting
formulae. But now I have a normalized value i can sum and reapply conditions
to get a "normalized group stoplight"

looks great and very easy to control.. thanks again