Formula Help to Match Multiple Results and Average
I work for a school district and need some help with a new requirement we were just given for scoring some student scores.
The spreadsheet (see link below) was created to keep track of students test scores for various things. This spreadsheet will contain students information and a student will be shown more than once in the spreadsheet. The Student ID is what we will key off of to find the multiple instances of the student.
What we need to be able to do is some how perform a search for these multiple entries of each student and when found, average the 2 scores for each 7 test categories. The result needs to be a single line for each student that gives the student name, student id, and the 7 test category averages.
Any help of where to start and how to accomplish this task is greatly appreciated.
Spreadsheet: http://1drv.ms/1dj6RMo
This is a kind of weighted average
I would calculate this as a weighted average by StudentID.
Do you need to:
a)
1. calculate total of module 1 for each student
2. calculate total of module 2 for each student
3. calculate average of those two totals for each student
Use weighted average (AVERAGEIF of module1 by studentID ) and AVERAGEIF of module2 by studentID as argument in formula used in "Growth" column. You can accomplish this by pivot table.
or:
b)
1. Calculate average of "Growth" column for each student. - then use AVERAGEIF by studentID
c) something else ?
It's going to be a little
It's going to be a little different. So for this go around a student MIGHT be listed more than once. If they are, we need to average their module 1 totals (we will eventually need to this with module 2 as well). After that is averaged, we need to add each student to a spreadsheet so they are only listed once with their name, student id, and each of the 7 averages. Here is a link to a spreadsheet with some test data in it to give you a better idea: http://1drv.ms/1cr3dCm