Giving grades a numerical value

Hi all,

As part of a bigger document that shows students the grade they have achieved after every 8 weeks ("a module") I came across a problem with the maths part of working out the students grade.

The students are assessed over 4 different topics (A, B, C & D) all of which are worth equal weighting.

Each student receives a "fine level" from a drop down list from cell AD4:AD25.

I gave each fine level grade a numerical value, seen in cell I5.

The issue that occurred this year is when a student receives different fine levels, the 'current working level' is skewed. The example shown in the attached should show 'Prep3' instead of 'Prep2'.

I tried using an average in cell I10 instead but still could not work it out.

I would like a much more robust way of working out the 'current working level' as an "average" from levels 'A' 'B' 'C' 'D' instead of a skewed level.

Any help much appreciated.

First of all, you can simplify the formula you have in I5 to this:

=IF(E5="NA",0.1,MATCH(E5,AD$6:AD$25,0))

then copy down to I8. You can change the formula in I10 to this:

=AVERAGE(I5:I8)

and then you can change the formula in E10 to this:

=IF(I10<1,"NA",INDEX(AD$6:AD$25,INT(I10)))

Hope this helps.

Pete