Applying multiple distinct conditional formatting rules

[javier]

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 (let’s say 2 std devs? but I’m 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?

 

[JLatham]

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]

Cheers, J, but I think I mis-communicated the issue.

I’m running Excel 2007, which allows multiple conditions. What I do not
know how to do – if it’s 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]

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.