Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Weighted Grades Worksheet
I really thought this would be a relatively simple search...figured there would be examples all over the place. But I haven't been able to find exactly what I'm looking for, so I thought I might ask for some 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 great if I could put something together that shows the actual formula in the 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 was miscalculated. I don't think it was intentional, I just want to have my facts straight before I approach my instructor. Thanks for all the help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted Grades Worksheet
"JPCleary" wrote:
Subject: Weighted Grades Worksheet 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[i] * w[i]/tw, i=1,13) where "sigma" means "sum of", and "tw" is total weight, i.e. sigma(w[i], 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[i]/maxScore[i] * w[i]/tw, i=1,13) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted Grades Worksheet
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted Grades Worksheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weighted grades | Excel Worksheet Functions | |||
How do I calculate weighted averages of grades? | Excel Discussion (Misc queries) | |||
Grades | Excel Discussion (Misc queries) | |||
How to Weight Grades vs. Averaging Student Grades | Excel Programming | |||
grades | Excel Discussion (Misc queries) |