Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking System For Large Groups
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking System For Large Groups
You may want to take a look at the SUMPRODUCT function.
"Gupta A." wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking System For Large Groups
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. -- Gary''s Student - gsnu200723 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking a List which must be within GROUPS | Excel Discussion (Misc queries) | |||
AutoFilter Best Practice when used in large files (slow system dow | Excel Discussion (Misc queries) | |||
divide a large group of people into smaller groups by their vote | Excel Discussion (Misc queries) | |||
excel causing system to be in low system resource | Excel Discussion (Misc queries) | |||
how do i view all groups under excel in google groups | Excel Discussion (Misc queries) |