Conditional formatting to reflect exam grade compared to predicted grade

Morning everyone,

I would really appreciate a bit of help formatting a spreadsheet to analyse exam performance (see example attached).

I've already used Excel 2010 to place conditional formatting on the sheet (different rules for questions with different max marks as notes to the side of the main table). I'm not sure I've done this as efficiently as possible - I used the conditional formatting toolbar button and the built-in functions - seemed very sluggish - but its done and it works.

What I still need to do is conditionally format the grade of the exam compared to the predicted grade for each pupil.

GCSE grades are on the scale A*, A, B, C, D, U for this paper. I tried to use VLOOKUP and IF to assign a numerical value to a grade and then do a simple sum, the idea being to colour the box/text with the result...but can't get it to work and don't really have the time to persevere...so here I am!

I'm sure this is pretty straightforward, but would appreciate some help learning how to conditionally format/compare non-numerical values!

Thanks,

PM

That was what I was trying to do with VLOOKUP and IF...but I can't get hte formula to resolve and then am not sure how to apply the conditional formatting...

P

@ dogberry

=A1="Dog" !!

Pl see the attached file.

Hi strangecharm

I am not quite sure what you mean, but see the attached. Column Z has been conditional formatted for the grade less/equal/greater then the predicted grade!!

might be a starter, if not post query.

Kevin

Thanks kvsrinivasamurthy - helpful to see an example of how to colour cells based on the grade. The syntax is unfamiliar to me!

P

Hi Kevin,

Thanks for the < = > sheet - I need to be able to indicate that a grade is 1,2,3 levels above/below target. From your example it seems like Excel can resolve A<C though - I thought I would have to ascribe numerical values with VLOOKUP?

P