weighted avg or =sumproduct

  • Thread starter Thread starter battycat
  • Start date Start date
B

battycat

I need to calculate weighted grades. Can you help?

score of 90 pts Score of 35 total pts
Jane 90 50% of grade 30 50% of grade
Joe 77 30
George 66 31
William 87 33
Mary 79 35


How do I easily calculate the total grade for these
folks? Should I use =sumproduct?
 
Battycat,

I didn't fully understand your question below.

But to get the weighted average of grades it is simply as follows:

=Grade * Weight + Grade * Weight + ...
=65%*10%+70%*15%+80%*15%+73%*10%+80%*50%
=76.3% average score

It is sum of the grades times the weights. You might as error check, add
the weights to ensure that the weights total to 100%

Hope that helps.

Regards,
Kevin
 
Kevin,
Is there a way to do this using the =sumproduct formula?
Jane scored 90points out of 90 (100% on the first test)
worth 50% of her final end of term grade. The next test,
Jane scored 30points out of 35 (85% on the second test)
and that score is worth 50% of her final grade. Is
there a clean way to set this up and use =sumproduct?
Battycat
 
Battycat,

Assuming that your data looks like this:

Student Mark1 Max1 Mark1 - % Weight1 Mark2 Max2 Mark1-%
Weight2 GRADE
Jane 90.00 100.00 90% 50% 30.00 35.00 86% 50%
87.86%


in cell J2 (GRADE - currently reading 87.86%) you can use the formula
=SUMPRODUCT(D2,E2)+SUMPRODUCT(H2,I2)

HTH,
Katherine
 
The general formula for a weighted average is =SUMPRODUCT(Data,Weights)/SUM(Weights). You can
eliminate the division if the sum of the weighting factors is 1 (100%).

Therefore if your weights are percentages, with rounded scores in A1:A2 and percentage weights
in B1:B2,

=SUMPRODUCT(A1:A2,B1:B2)

That gives me 93% with your data.
 
Hi Myrna,

I didn't fully understand battycat's question, so I took a guess at it.

I am not sure if battycat wants the average of the class, or the averages
(plural) for each student.

If battycat is looking for the average for each student, I don't see an easy
way to use sumproduct other than how Katherine formulated her answer. But
given how the data is given, it does seem to be a bit awkward to me.

I believe your solution "=SUMPRODUCT(A1:A2,B1:B2)" would give the average
for two students. The grades would be A1:A2 for the two students, and the
weights would be B1:B2.

I am a bit confused as to what is being requested or desired by battycat.

Regards,
Kevin









Myrna Larson said:
The general formula for a weighted average is
Click to expand...
=SUMPRODUCT(Data,Weights)/SUM(Weights). You can
eliminate the division if the sum of the weighting factors is 1 (100%).

Therefore if your weights are percentages, with rounded scores in A1:A2 and percentage weights
in B1:B2,

=SUMPRODUCT(A1:A2,B1:B2)

That gives me 93% with your data.
Click to expand...
 
I'm assuming A1 contains the grade on the 1st exam for the 1st student, A2 contains the grade on
the 2nd exam for that same student. B1 and B2 would contains the weights for these exams, 50%
each in the example.


Hi Myrna,

I didn't fully understand battycat's question, so I took a guess at it.

I am not sure if battycat wants the average of the class, or the averages
(plural) for each student.

If battycat is looking for the average for each student, I don't see an easy
way to use sumproduct other than how Katherine formulated her answer. But
given how the data is given, it does seem to be a bit awkward to me.

I believe your solution "=SUMPRODUCT(A1:A2,B1:B2)" would give the average
for two students. The grades would be A1:A2 for the two students, and the
weights would be B1:B2.

I am a bit confused as to what is being requested or desired by battycat.

Regards,
Kevin









Myrna Larson said:
The general formula for a weighted average is
Click to expand...
=SUMPRODUCT(Data,Weights)/SUM(Weights). You can
Click to expand...
 
I need to calculate weighted grades. Can you help?

score of 90 pts Score of 35 total pts
Jane 90 50% of grade 30 50% of grade
Joe 77 30
George 66 31
William 87 33
Mary 79 35

How do I easily calculate the total grade for these
folks? Should I use =sumproduct?
Click to expand...

If this is your data layout, you're only hurting yourself. Yes, a complicated
formula could be written to handle this, but no useful purpose would be served
developing it. Efficient data structures make many calculations effortless.

If you were using a better data layout like

Max Points 90 35
Weight 50% 50%

Jane 90 30
Joe 77 30
George 66 31
William 87 33
Mary 79 35

where, e.g., Max Points would be in A1, the 50%s would be in B2 and C2, Jane in
A4, and Mary's 35 score on the second exam in C8, with A3:C3 blank and the
resulting weighted average score on a percentage basis, you could use the
following in D4 for Jane's average score.

=SUMPRODUCT(B4:C4,1/B$1:C$1,B$2:C$2)
 
Back
Top