Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summarizing conditional formats
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summarizing conditional formats
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" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summarizing conditional formats
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summarizing conditional formats
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" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summarizing conditional formats
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. "Joel" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summarizing conditional formats
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 "Joel" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
6 CONDITIONAL FORMATS | Excel Discussion (Misc queries) | |||
Conditional formats- paste special formats? | Excel Discussion (Misc queries) | |||
paste conditional formats as formats | Excel Discussion (Misc queries) | |||
4 conditional formats? | Excel Worksheet Functions | |||
More than three conditional formats? | Excel Discussion (Misc queries) |