weighted marks for school (each mark its own weighting)

[SandroTrunz]

Dear community,



I've long been searching for a suitable spreadsheet in helping me calculate the final marks for the term / semester report. As a language teacher, I commonly end up with marks for:

- vocabulary

- grammar

- reading

- writing

- listening

- speaking

As not all exams are equally big and/or important, and as I usually end up with tons of vocabulary tests and few writings, for instance, each grade must be assigned its individual weighting - or 'importance' so to speak.

I've tried for quite a while, but the result is too complicated and - if I'm not extra careful - easily leads to errors or malfunctions. I should have written down all the other commands I'd come across while researching feasible solutions that other had suggested online. I'm 100% sure that some of theses would yield a way more satisfying result.

One sheet contains data such as Name 'main class', 'level in French' etc. You best just ignore that.
The fields 'AJ' to 'BJ' where a trick I used to avoid that, if the field should remain empty (student didn't sit the exam), Excel doesn't spit out an error or count it as 0 (zero).

You can find and download the spreadsheet here: Weighted Grades.xlsx.

Would be absolutely awesome if you could help.
Many best regards, thank you kindly in advance, and I hope you're well.
Sandro

[TMS]

Try:

Please Login or Register  to view this content.

copied down.

[6StringJazzer]

I'm not sure I understand your question since you have a working solution for what you have described. I have provided a simplified approach for the final grade, but the results are the same. The way that weighted averages are done is to multiply each score times its weight, then add the results. This is exactly what SUMPRODUCT does. (It omits any blank grades from the calculation.) Divide this sum by the total of the weights (also omits blank grades). This formula does not require the helper columns in AJ:BF.

Is this what you're asking?

[TMS]

Better:

Please Login or Register  to view this content.

not using the helper columns.

[SandroTrunz]

Dear all,

Thank you so much for your swift replies. I'll have a cheeky gander tomorrow - it's been a long day and late by now.
You're true-blue legends!

Sandro

[TMS]

You're welcome. Thanks for the rep.