Conditional formatting comparing two cells

[jef]

I need help with a Conditional Format formula for the following. I
have listed letter grades (A, A-, B+, B, …etc.) in cells H2:H13. I am
comparing two, side-by-side time periods (cells) as follows:

IF current period in the first column is C- or below AND previous
period in the second column is C or above, then this is bad, and I
want that cell to reflect a color.

or

IF current period in the first column is C or above AND previous
period in the second column is C- or below, then this is good, and I
want that cell to reflect a color.

All other conditions would reflect nothing, so the cell color would
remain as it normally is.

I know I can have three “conditional formats” per cell.

Thanks for your help.

jef

 

[Pete_UK]

Can you tell us which columns you are comparing?

Is the list of grades in H2:H13 used for validation or for determining
the rank etc. (rather than being one of the columns), i.e. is it for
reference?

Basically, you want one colour if there is an improvement in grade and
another colour if the grade has diminished, but only from grade C ?

Pete

 

[wolfgang werner]

jef,

You have one major problem. Attempting to compare characters (where for example "C" evaluates to greater than "C+") requires a more complex solution. Where you to change to numeric values the the solution is to use the conditional format directly. Assuming current column is C and previous is B then in cell C1 conditional format entries would be:
=C1>B1 assign the color green
=C1<B1 assign the color yellow
Then copy (format) C1 to all the other cells. This is good for as many column of grades you create.

If you insist using alphabetic grades with +/- then you will need to use a sorted look up table. The easiest way is to: (1) list all grades by row and "fill" the next column with sequential numbers. (2) Sort the alphabetic grades "ascending" (along with the numeric equivalents. (3) Name the alphabetic grades (select the cells) "grade" (exclude the "). (4) Name the numeric cells "value". Now, in cell C1 the conditional format entries should be:
=LOOKUP(B1,grade,value)<LOOKUP(C1,grade,value)
assign the color green
=LOOKUP(B1,grade,value)>LOOKUP(C1,grade,value
assign the color yellow
Then copy (format) C1 to all the other cells. This is good for as many column of grades you create.

You might need to change the greater/less signs depending on how you sequenced the grades (I assumed F- = 0 and A+ = 14)



jef wrote:

Conditional formatting comparing two cells
02-Oct-09

I need help with a Conditional Format formula for the following.
have listed letter grades (A, A-, B+, B, =85etc.) in cells H2:H13. I a
comparing two, side-by-side time periods (cells) as follows

IF current period in the first column is C- or below AND previou
period in the second column is C or above, then this is bad, and
want that cell to reflect a color

o

IF current period in the first column is C or above AND previou
period in the second column is C- or below, then this is good, and
want that cell to reflect a color

All other conditions would reflect nothing, so the cell color woul
remain as it normally is

I know I can have three =93conditional formats=94 per cell

Thanks for your help

jef

EggHeadCafe - Software Developer Portal of Choice
Silverlight Multipurpose WebRequest Proxy
http://www.eggheadcafe.com/tutorial...12-003fe889ed4a/silverlight-multipurpose.aspx