Calculating Target/Actual Grades

Dear All

I have a spreadsheet which lists students, their target grade and their actual grade as follows:

Person 1 | A | C
Person 2 | B | B
Person 2 | C | B

etc etc

Please can you help me:

1) Highlight as red those grades which are on target or above target;
2) Leave as white those grades which are one grade below target;
3) Highlight as blue those grades which are two or more grades below target;
4) Count the number of 'red' students and the number of 'blue' students.

I would be very grateful for any help/advice you can offer.

Many thanks

Daniel

djs25uk,

Attached is an example workbook based on the sample criteria provided. I used a table to assign each Letter Grade with a Numeric Value. Named that table GradeVals.

Using that table, I was able to add a helper column to that contains a vlookup formula to determine if the row should be red, blue, or left alone:

=IF(VLOOKUP(C6,GradeVals,2,FALSE)>=VLOOKUP(B6,GradeVals,2,FALSE),"red",IF(VLOOKUP(C6,GradeVals,2,FALSE)<=VLOOKUP(B6,GradeVals,2,FALSE)+2,"blue",""))

Once the helper column formula was copied down, the conditional formatting and countif formulas were easy.

Hope that helps,
~tigeravatar

Thanks tigeravatar! The example is an enormous help. I noticed that the red and blue were evaluating correctly but when they grade is one below, it is not being left unhighlighted. I tried changing your example (playing with the +2 in the equation) but it didn't appear to make any difference. I'll keep on playing around with it.

Many thanks for your help

Daniel

A good night's sleep has obviously helped. I have changed the evaluation of the "blue" to as follows and it works:

=IF(VLOOKUP(C9,GradeVals,2,FALSE)>=VLOOKUP(B9,GradeVals,2,FALSE),"red",IF(VLOOKUP(C9,GradeVals,2,FALSE)+1<VLOOKUP(B9,GradeVals,2,FALSE),"blue",""))

Thank you very much for your help!!

Daniel

I've used a named range to do my caparisons. it removes the need for alot of vlookups
Named range = GRADES

={"AS","A","B","C","D","E","F","G","N","U"}

Then i can use conditional formatting using a formula ie

=MATCH(A2,GRADES,FALSE)=MATCH(B2,GRADES,FALSE)

.
altering the = for < or> depending on the rule.

countifs can then be done using the same principals