Weighted Grades Worksheet

[JPCleary]

I really thought this would be a relatively simple search...figure
there would be examples all over the place. But I haven't been able t
find exactly what I'm looking for, so I thought I might ask for som
help.

See attached screenshot...it should be pretty self explanatory.

Using some online calculators, I think the answer is a grade of 96.7%.
But I want to be able to prove the formula. To that end, it would grea
if I could put something together that shows the actual formula in th
formula bar...rather than excel function, if you understand what I mean
But I'll take what I can get.

The reason I am putting this together is I think my grade wa
miscalculated. I don't think it was intentional, I just want to have m
facts straight before I approach my instructor.

Thanks for all the help

+-------------------------------------------------------------------
|Filename: Grades.JPG
|Download: http://www.excelbanter.com/attachment.php?attachmentid=676
+-------------------------------------------------------------------

 

[joeu2004]

See attached screenshot...it should be pretty self explanatory.
Using some online calculators, I think the answer is a grade of 96.7%. [....]
|Download: http://www.excelbanter.com/attachment.php?attachmentid=676|

Actually, no it is not, IMHO.

The weights do not make sense to me. Normally, weights expressed as
percentage should sum to 100%. Yours sum to 320%. Not a problem
computationally: we simply divide the individual weights by their sum. But
it makes suspicious of the data.

Furthermore, we have insufficient information to compute the weighted
average as a percentage. You provide scores received in row 2. We would
also need to know the maximum scores for each column.

The weighted average of the scores received is:

sigma(score * w/tw, i=1,13)

where "sigma" means "sum of", and "tw" is total weight, i.e. sigma(w,
i=1,13).

In Excel, this can be calculated by:

=SUMPRODUCT(B2:N2,B3:N3)/SUM(B3:N3)

The result is 96.75.

If we assume a max score of 100 for each column, the weighted average grade
is 96.75/100, i.e. 96.75%, close to your expectations.

Note: If the max score were different for each column, we could not simply
divide the weight average score by anything. Instead, we would need to
compute the weighted average percentage as follows:

sigma(score/maxScore * w/tw, i=1,13)

 

[GS]

In my student grades manager app, scores can be comprised of any number
of individual score components *but* the average is what determines the
weighted score for that module those components belong to. For example,
a course module consisting of 11 components each worth 20 marks means
the total available score for that module is 220. If a student scores a
total of 198 then the average is 90% (198/220*100%).

If the module has a --weight value-- of 20(%) (value is out of possible
total of 100%, as pointed out by joeu2004) then the --weighted score--
for that module is 18(%). If the same student scored 90% in another
course module with a --weight value-- of 30(%) the --weighted score--
will be 27%, giving this student a rolling average of 45%. There is
another 55% of --weight values-- yet to be used by other achievement
score modules.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

GS fixed some typos :

In my student grades manager app, scores can be comprised of any number of
individual score components *but* the average is what determines the weighted
score for that module those components belong to. For example, a course
module consisting of 11 components each worth 20 marks means the total
available score for that module is 220. If a student scores a total of 198
then the average is 90% (198/220*100%).

If the module has a --weight value-- of 20% (value is out of possible total
of 100%, as pointed out by joeu2004) then the --weighted score-- for that
module is 18%. If the same student scored 90% in another course module with
a --weight value-- of 30% the --weighted score-- will be 27%, giving this
student a rolling average of 45%. There is another 55% of --weight values--
yet to be used by other achievement score modules.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion