Is it possible to apple two Conditional Formatting rules to one cell

Question

Gwyn Plem Author

Level 2

164 points

Is it possible to apple two Conditional Formatting rules to one cell

Hi

We are tracking pupil grades in school. There are two factors I would like highlighted in a cell

For example -

1) If the pupil has scored above 90% - colour cell green (Or red if below 90%)

2) If the score is less than his previous reult - text to red (or green if he is better than last test)

Both rules are needed - as a child could score below 90%, but be on an upward grade trend, which the green text colour would demonstrate. Only one rule does not give a sufficient picture.

Is this possible? Numbers seems to apply only one rule. Am i doing something wrong or should I be approaching the issue from another angle?

Thanks in advance for any advice

MacBook Pro, Mac OS X (10.7.2)

Posted on Apr 27, 2016 7:50 AM

Reply

Answer 1

SGIII

Level 8

35,252 points

Apr 27, 2016 10:38 AM in response to Gwyn Plem

It's possible of course to have multiple rules. But as far as I can tell the rules won't set both fill color and text color. It seems that the one that comes first gets set, and the second is ignored. I tried dragging the rules into a different order, etc. Maybe someone else knows a way to do what you want with Conditional Highlighting. (A script can definitely handle this).

SG

Reply

Answer 2

Yellowbox

Level 7

20,329 points

Apr 28, 2016 2:01 AM in response to Gwyn Plem

Hi Gwyn,

There are four rules:

1) If the pupil has scored above 90% - colour cell green (Or red if below 90%)

2) If the score is less than his previous reult - text to red (or green if he is better than last test)

After playing with this problem, I must agree with SG about multiple rules. They have a hierarchy.

I added two tables. These can be "hidden" (Cut and Paste to another sheet after you get this document to work).

The "Excellent Score" table allows you to enter another value, in case you decide to vary the threshold. Conditional Highlight rules will adjust, no need to edit them.

The bottom table will show the Cell Fills (we shall slide it under the "Progress" table).

Formula in B2 of the bottom table (and Fill Right and Fill Down)

=IF(Progress::B2≥Excellent Score::$A$1,"Green","Red")

Conditional Highlighting for the bottom table:

To change both the Fill and Text colours, use a Custom Style (drag the panel up to see it).

I used the rainbow wheel next to Text and Fill. That gives more colour choices than the default colour palette. Set the Cell Fill to the same as Text Colour so that the text will blend into the background. Choose a strong colour (the "Progress" table text will be a light colour to stand out).

Conditional Highlighting in C2 of the "Progress" table

Select all Body Cells of the "Progress" table and Combine Rules.

Slide the Cell Fill table behind the Progress table.

Hmm... needs some adjustments to the colours to give more contrast.

How about grey fills?

Regards,

Ian.

Reply