Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying multiple distinct conditional formatting rules
I have a table full of data (well, the results of formulae), where each row
has nice points of comparison (either Y or N; or strongly disagree, disagree, neutral, agree or strongly agree). What I had hoped to do was use Conditional Formatting to highlight those values - within each row - that for each of the nine comparisons is significantly higher than average (lets say 2 std devs? but Im not fussy). For instance, if 100 people said yes and none said no, I'd want the 'yes' to be highlighted... But if 60 people said yes and 40 people no, I don't care. Is this possible without my having to set Conditional Formatting hundreds of individual times, and if so, how? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying multiple distinct conditional formatting rules
You didn't mention which version of Excel you are using. If it is 2003 or
earlier, you can only have 4 conditionals (default plus 3 based on conditions) without resorting to VB coding. Sounds like you might be able to get away with that limitation since some of the results are "don't care" situations (leave as default). I'd set up a helper column and put your standard deviation formula into it, then for the actual results cells, set your conditions to evaluate the appropriate cell in the helper column and change appearance based on that result. "javier" wrote: I have a table full of data (well, the results of formulae), where each row has nice points of comparison (either Y or N; or strongly disagree, disagree, neutral, agree or strongly agree). What I had hoped to do was use Conditional Formatting to highlight those values - within each row - that for each of the nine comparisons is significantly higher than average (lets say 2 std devs? but Im not fussy). For instance, if 100 people said yes and none said no, I'd want the 'yes' to be highlighted... But if 60 people said yes and 40 people no, I don't care. Is this possible without my having to set Conditional Formatting hundreds of individual times, and if so, how? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying multiple distinct conditional formatting rules
Cheers, J, but I think I mis-communicated the issue.
Im running Excel 2007, which allows multiple conditions. What I do not know how to do €“ if its possible €“ is to apply 3 conditions, three times each, within each row, and then copy those nine conditions down along each row, but without having the condition calculate based upon on the values of the whole table. Does that make more sense? "JLatham" wrote: You didn't mention which version of Excel you are using. If it is 2003 or earlier, you can only have 4 conditionals (default plus 3 based on conditions) without resorting to VB coding. Sounds like you might be able to get away with that limitation since some of the results are "don't care" situations (leave as default). I'd set up a helper column and put your standard deviation formula into it, then for the actual results cells, set your conditions to evaluate the appropriate cell in the helper column and change appearance based on that result. "javier" wrote: I have a table full of data (well, the results of formulae), where each row has nice points of comparison (either Y or N; or strongly disagree, disagree, neutral, agree or strongly agree). What I had hoped to do was use Conditional Formatting to highlight those values - within each row - that for each of the nine comparisons is significantly higher than average (lets say 2 std devs? but Im not fussy). For instance, if 100 people said yes and none said no, I'd want the 'yes' to be highlighted... But if 60 people said yes and 40 people no, I don't care. Is this possible without my having to set Conditional Formatting hundreds of individual times, and if so, how? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying multiple distinct conditional formatting rules
That shouldn't be a problem. Any given cell's conditional format can be set
independently of any other cell's. I haven't done much with conditional formatting in 2007 -- all but a very few of my clients are on earlier versions of Excel - so unless MSFT really went bananas in setting up all of the automatic rainbow and other nice eye candy formatting for ranges and won't let you override individual cells within some autoformatted area, I don't see why you can't do it. I would have to have a better idea of HOW you've got your table formatted now to begin to tell you the HOW of what you want to do at this point. Sorry to kind of drop out on you this way since I realize that it is the HOW of it that you need help with. I would think that it should just be a matter of first deciding on the rules you need to use to highlight those particular cells and then selecting Conditional Formatting and the Highlight Cells Rules from the [Home] ribbon and setting the rules and formatting for each that you want. "javier" wrote: Cheers, J, but I think I mis-communicated the issue. Im running Excel 2007, which allows multiple conditions. What I do not know how to do €“ if its possible €“ is to apply 3 conditions, three times each, within each row, and then copy those nine conditions down along each row, but without having the condition calculate based upon on the values of the whole table. Does that make more sense? "JLatham" wrote: You didn't mention which version of Excel you are using. If it is 2003 or earlier, you can only have 4 conditionals (default plus 3 based on conditions) without resorting to VB coding. Sounds like you might be able to get away with that limitation since some of the results are "don't care" situations (leave as default). I'd set up a helper column and put your standard deviation formula into it, then for the actual results cells, set your conditions to evaluate the appropriate cell in the helper column and change appearance based on that result. "javier" wrote: I have a table full of data (well, the results of formulae), where each row has nice points of comparison (either Y or N; or strongly disagree, disagree, neutral, agree or strongly agree). What I had hoped to do was use Conditional Formatting to highlight those values - within each row - that for each of the nine comparisons is significantly higher than average (lets say 2 std devs? but Im not fussy). For instance, if 100 people said yes and none said no, I'd want the 'yes' to be highlighted... But if 60 people said yes and 40 people no, I don't care. Is this possible without my having to set Conditional Formatting hundreds of individual times, and if so, how? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting conditional formatting rules | Excel Discussion (Misc queries) | |||
Paste conditional formatting rules | Excel Discussion (Misc queries) | |||
Applying Conditional Formatting to Multiple Worksheets - Excel 200 | Excel Discussion (Misc queries) | |||
More than 3 rules of conditional formatting | Excel Discussion (Misc queries) | |||
I need 5 conditional formatting rules-excel only allows 3 | Excel Discussion (Misc queries) |