I am processing student exam results. In each row I have a student name followed by various letters/grades in the following columns (one column per subject). Elsewhere in the document I have a conversion table showing what each grade is worth in a separate points system (e.g. A = 52pts, B = 46 pts, etc). Some of the columns are blank as not all students do all subjects. See below
E.g.
Name Maths English Science Drama Total Points Average Points
Joe A B A C ?? (e.g 52+46+52+40 = ??) ?? (e.g. average of 52,46,52,40)
Adam B B D - ?? (e.g. 46+46+34 = ??) ?? (e.g. average of 46,46,34)
Table:
Grade Points
A 52
B 46
C 40
D 34
What I need is a formula for the total points column which looks up each value from the row, works out the points score (by looking in the VLOOKUP table) and adds them together to form a total score. I know how to do it if I add an extra column for each Subject, and then add the scores separately but I'm sure there is a neater way of doing it in one column? I'd also like a similar thing with the average too. I think it may have something to do with an array formula but I've not used them before?
Hope you can help!
Bookmarks