I have the following problem: a table contains names of pupils in the first two columns (A and B). The next columns are:
- a code for the type of grade (e.g. M for oral exam, T for test, etc.)
- the integer part of the grade (as a number, not as A, B, an so on)
- the decimal part of the grade (believe it or not, in some countries they actually do that)
- the percentage, which is kind of irrelevant in this question.
So basically there are grades in four-column-ranges or blocks, starting from column 3 (C) and going to the end of the long table.
What I would like to do is calculate the average grades for a certain type (like the average of all Ms). How do I look up what is in the first grade-column and decide if I want to add the second and third columns to the total, depending on whether that’s an M or not? And, obviously, I would need to count the Ms too, but that’s probably easier.
(Edited to add this example)
(Second edit: It may very well be that this is not the best way to organize this type of data for this problem, in which case I am open also to suggestions on how to improve the design itself)