Ranking System For Large Groups

[Guest]

If I have a class of students, say A-F and they have different grades in
different subjects. Everytime they take a new test I give them a new grade
and I keep an average of all the grades for each student. In a ranking table
I use a macro to sort the grade of the students in ascending order. They have
grades for multiple subjects so I have multiple rankings. Now if I want to
give them one point for being in the lowest of a group, and 6 for being the
highest of the group and I wish to sum up all these rankings to find out waht
their average ranking is - well one way is the countif function, and for
every row I count the number of times that name appears and multiply it by
the number of poitns I wish to award. However if I have more than a hundred
students that becomes impractical. Is there a way to count the number of
times a name appears in a row and to multiply it by a number in a quicker
way? is there a better way to do this?

 

[Guest]

You may want to take a look at the SUMPRODUCT function.

 

[Guest]

May I suggest an alternative approach?

If your raw data is of the form-> Student Subject Grade:

Student Subject Grade
John Literature 79
Trevor Math 93
Trevor Literature 95
John History 78
Mary Math 74
John Literature 76
Trevor Literature 96
John History 93
Trevor History 70
Mary History 89
Trevor Math 84
John Literature 100
Mary Math 78
Mary Math 81
Mary Literature 86
Mary History 77
John Math 78
Trevor Math 76
Trevor Literature 87
Mary History 78
Mary Literature 74
Trevor History 83
Trevor History 77
Mary Literature 73
John History 100
John Math 90
John Math 82

First create a Pivot Table displaying the Sum of grades by student by subject:

Sum of Grade Subject
Student History Literature Math Grand Total
John 271 255 250 776
Mary 244 233 233 710
Trevor 230 278 253 761
Gra Total 745 766 736 2247

Now all you need to do is to use the RANK function on each column to
determine the student's relative ranking.