Calculating Target/Actual Grades

[djs25uk]

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

[tigeravatar]

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:

Please Login or Register  to view this content.

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

Hope that helps,
~tigeravatar

[alansidman]

Daniel;

This link has a section on Conditional Formatting.

http://www.techonthenet.com/excel/questions/index.php

I think that you will need to convert your Alpha grades to numericals to do some of the calculations, ie. A=5, B=4, C=3, etc. Otherwise how will excel understand greater than or less than.

Alan

[djs25uk]

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

[djs25uk]

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

[ianh]

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

Please Login or Register  to view this content.

Then i can use conditional formatting using a formula ie

Please Login or Register  to view this content.

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

countifs can then be done using the same principals