Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Weight Grades vs. Averaging Student Grades
Hello Programmers,
I want to modify a grade-tracking sheet that scores students based on points: Assignments are entered into columns with possible point values in row 10, and students' points are entered below (each student on every other row). Formulas: --Total points possible are in col. O: =SUMIF(AO14:AP14,"-.99",AO $10:AP$10) --Total points earned are in col. P: =SUMIF(AO14:AP14,"-. 99",AO14:AP14) --Total averaged assignment %s earned are in col. Q: {=IF(SUMIF($AO14:$AP14,"-1",$AO$10:$AP $10)=0,"",SUMIF($AO14:$AP14,"-1",$AO14:$AP14)/SUMIF($AO14:$AP14,"-1", $AO$10:$AP$10)*100)} What I'd like is for assignments to somehow be weighted based on their point value. For instance, say there are these assignments and possible point values: --Assign names in Row 9: Assign1 Project Report Quiz UnitTest --Assign points in Row 10: 10 25 20 50 100 So, there are 205 points possible. The formula above simply averages scores together, but I want the 100 point assignment to = 48.78% of the grade, the 50 point assign to = 24.39% of the grade, etc. Also, not every student will do every assignment-there could be null cells in the student rows. Thanks very much Arnold |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Weight Grades vs. Averaging Student Grades
Isn't it (Column P /Column O) ?
"Arnold" wrote: Hello Programmers, I want to modify a grade-tracking sheet that scores students based on points: Assignments are entered into columns with possible point values in row 10, and students' points are entered below (each student on every other row). Formulas: --Total points possible are in col. O: =SUMIF(AO14:AP14,"-.99",AO $10:AP$10) --Total points earned are in col. P: =SUMIF(AO14:AP14,"-. 99",AO14:AP14) --Total averaged assignment %s earned are in col. Q: {=IF(SUMIF($AO14:$AP14,"-1",$AO$10:$AP $10)=0,"",SUMIF($AO14:$AP14,"-1",$AO14:$AP14)/SUMIF($AO14:$AP14,"-1", $AO$10:$AP$10)*100)} What I'd like is for assignments to somehow be weighted based on their point value. For instance, say there are these assignments and possible point values: --Assign names in Row 9: Assign1 Project Report Quiz UnitTest --Assign points in Row 10: 10 25 20 50 100 So, there are 205 points possible. The formula above simply averages scores together, but I want the 100 point assignment to = 48.78% of the grade, the 50 point assign to = 24.39% of the grade, etc. Also, not every student will do every assignment-there could be null cells in the student rows. Thanks very much Arnold |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Weight Grades vs. Averaging Student Grades
On Oct 17, 9:39 am, Arnold wrote:
Hello Programmers, I want to modify a grade-tracking sheet that scores students based on points: Assignments are entered into columns with possible point values in row 10, and students' points are entered below (each student on every other row). Formulas: --Total points possible are in col. O: =SUMIF(AO14:AP14,"-.99",AO $10:AP$10) --Total points earned are in col. P: =SUMIF(AO14:AP14,"-. 99",AO14:AP14) --Total averaged assignment %s earned are in col. Q: {=IF(SUMIF($AO14:$AP14,"-1",$AO$10:$AP $10)=0,"",SUMIF($AO14:$AP14,"-1",$AO14:$AP14)/SUMIF($AO14:$AP14,"-1", $AO$10:$AP$10)*100)} What I'd like is for assignments to somehow be weighted based on their point value. For instance, say there are these assignments and possible point values: --Assign names in Row 9: Assign1 Project Report Quiz UnitTest --Assign points in Row 10: 10 25 20 50 100 So, there are 205 points possible. The formula above simply averages scores together, but I want the 100 point assignment to = 48.78% of the grade, the 50 point assign to = 24.39% of the grade, etc. Also, not every student will do every assignment-there could be null cells in the student rows. Thanks very much Arnold You need to divide the individual grades by the total sum of the grades. For instance, if the grades you used in your example are in A10, B10, C10, D10, and E10 respectively, you would use a formula such as this to get the percentage the 100 grade is of the total: =E10/SUM(A10:E10) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
averaging a column of letter grades into a numeric GPA | Excel Worksheet Functions | |||
Grades | Excel Discussion (Misc queries) | |||
averaging class grades | New Users to Excel | |||
average student grades | New Users to Excel | |||
Vlookup student grades | Excel Discussion (Misc queries) |