How to Weight Grades vs. Averaging Student Grades

[Arnold]

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

 

[Guest]

Isn't it (Column P /Column O) ?

 

[JW]

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)