Conditional formatting

[staffrmj]

Can I use conditional formatting to compare text values in Excel?

I want to compare student grade performance over time and want to show
if a student's grade has increased or decreased e.g. A - B would be a
decrease, B - A would be an increase.

Thanks

 

[DKS]

Can you give more information on the layout of your sheet? Because the
formula could be different depending on the layout.

Let me explain:

Is your sheet with one row per student and different columns giving
different periods?

Or is your sheet with one row per student per period? Meaning you could
have several rows with the same student but for different periods? If this
is the case then are your data sorted as per period or not?

 

[staffrmj]

Can you give more information on the layout of your sheet?  Because the
formula could be different depending on the layout.

Let me explain:

Is your sheet with one row per student and different columns giving
different periods?

Or is your sheet with one row per student per period?  Meaning you could
have several rows with the same student but for different periods?  If this
is the case then are your data sorted as per period or not?







- Show quoted text -

The sheet has one row per student and records performance in the
different terms in different columns.

 

[Bernard Liengme]

Let's say first student is in A2 with grades in B2:K2
Select C2:K2
Use Format | Conditional Formatting
For improving grade use Formula Is: C2<B2 and make green
For lowering grade use Formula IS: C2>B2 and make red

Note the logic in Excel is the opposite of what is in your head because for
Excel "A" is less than "B" in the sense that it has a lower ASCII value.

Works with single grades (will not detect A+ differs from A) - that would
need a more complex formula

best wishes

 

[staffrmj]

Let's say first student is in A2 with grades in B2:K2
Select C2:K2
Use Format | Conditional Formatting
For improving grade use Formula Is: C2<B2 and make green
For lowering grade use Formula IS: C2>B2 and make red

Note the logic in Excel is the opposite of what is in your head because for
Excel "A" is less than "B" in the sense that it has a lower ASCII value.

Works with single grades (will not detect A+ differs from A) - that would
need a more complex formula

best wishes
--
Bernard Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme








- Show quoted text -

I do need to differentiate between A and A+ - any ideas gratefully
received!!

 

[Dave]

Hi,
One way would be to create a grade table in an out-of-the-way place on your
sheet. The table would assign a number to each grade. eg:
AA.........AB (Row refs)
A+..........1
A............2
A-..........3
B+..........4
B.............5
B-...........6
C+..........7
C............8
etc.
Lets say your table is in AA1:AB20
Then use VLOOKUP in your conditional formatting.
Copying from satffrmj:
Let's say first student is in A2 with grades in B2:K2
Select C2:K2
Use Format | Conditional Formatting
For improving grade use Formula Is:
=VLOOKUP(C2,$AA$1:$AB$20,2,0)>VLOOKUP(B2,$AA$1:$AB$20,2,0)
Make format green
For lowering grade use Formula Is:
=VLOOKUP(C2,$AA$1:$AB$20,2,0)<VLOOKUP(B2,$AA$1:$AB$20,2,0)
Make format red
Regards - Dave.

 

[Dave]

Oops - mixed up the "<" and ">" thingies.
For improving grade use Formula Is:
=VLOOKUP(C2,$AA$1:$AB$20,2,0)<VLOOKUP(B2,$AA$1:$AB$20,2,0)
Make format green
For lowering grade use Formula Is:
=VLOOKUP(C2,$AA$1:$AB$20,2,0)>VLOOKUP(B2,$AA$1:$AB$20,2,0)
Make format red
Dave.

 

[Shane Devenshire]

Hi,

Here is what you need:

Create a range in the spreadsheet that contains all the letter grades in
order:

A+
A
A-
B+
B
B-
C+
C
C-
D+
D
D-
F+
F
F-

You may not need A+ and F-. Suppose this is in the range L1:L15.

Assume your grades start in B2 and extend to the right say to J2.
Highlight the range C2:J2 (skipping B2) and choose Format, Conditional
Formatting, Formula is (from the first drop down). Enter the formula:
=MATCH(C2,$L$1:$L$15,0)<MATCH(B2,$L$1:$L$15,0)
set a color and click Add. Choose Formula is and enter the second formula:
=MATCH(C2,$L$1:$L$15,0)>MATCH(B2,$L$1:$L$15,0)
set a color.

You can make the formulas shorter by defining a range name, call it F for
the range L1:L15, then your formulas become:
=MATCH(C2,F,0)<MATCH(B2,F,0)
and
=MATCH(C2,F,0)>MATCH(B2,F,0)

If you want to keep the range L1:L15 reference on a different sheet than the
conditionally formatted cell you will need to use the range name approach
mentioned above. (When L1:L15 is selected click in the Name Box and type
the name you want to use.)

If you don't want a range in the spreadsheet to be used at all than choose
the Insert, Name, Define command and enter a name in the first box then in
the Refers to box enter:
={"A+";"A";"A-";"B+";"B";"B-";"C+";"C";"C-";"D+";"D";"D-";"F+";"F";"F-"}

Cheers,
Shane Devenshire
Microsoft Excel MVP
Join http://setiathome.berkeley.edu/ and download a free screensaver and
help search for life beyond earth.

 

[staffrmj]

Hi,

Here is what you need:

Create a range in the spreadsheet that contains all the letter grades in
order:

A+
A
A-
B+
B
B-
C+
C
C-
D+
D
D-
F+
F
F-

You may not need A+ and F-.  Suppose this is in the range L1:L15.

Assume your grades start in B2 and extend to the right  say to J2.
Highlight the range C2:J2 (skipping B2) and choose Format, Conditional
Formatting, Formula is (from the first drop down).  Enter the formula:
=MATCH(C2,$L$1:$L$15,0)<MATCH(B2,$L$1:$L$15,0)
set a color and click Add.  Choose Formula is and enter the second formula:
=MATCH(C2,$L$1:$L$15,0)>MATCH(B2,$L$1:$L$15,0)
set a color.

You can make the formulas shorter by defining a range name, call it F for
the range L1:L15, then your formulas become:
=MATCH(C2,F,0)<MATCH(B2,F,0)
and
=MATCH(C2,F,0)>MATCH(B2,F,0)

If you want to keep the range L1:L15 reference on a different sheet than the
conditionally formatted cell you will need to use the range name approach
mentioned above.  (When L1:L15 is selected click in the Name Box and type
the name you want to use.)

If you don't want a range in the spreadsheet to be used at all than choose
the Insert, Name, Define command and enter a name in the first box then in
the Refers to box enter:
={"A+";"A";"A-";"B+";"B";"B-";"C+";"C";"C-";"D+";"D";"D-";"F+";"F";"F-"}

Cheers,
Shane Devenshire
Microsoft Excel MVP
Joinhttp://setiathome.berkeley.edu/and download a free screensaver and
help search for life beyond earth.








- Show quoted text -

Thanks a lot - it worked a treat!