Saturday, October 6, 2012

Calculating Final Grades With Weighted Marks

Question:

I'm working on an assignment for my class and the question is..

11. Using a formula, calculate the final grades as a whole number out of 
100 (not a percentage).  The final grade should not have decimal places.  
Use the round function to accomplish this, rather then a formatting 
option. Use the following grading scheme 
a. Assignment 1 - 10% 
b. Assignment 2 – 15%
c. Assignment 3 – 20% 
d. Quizzes (Best 7) – 20% 
e. Final Exam – 35%

Use absolute cell referencing of the maximum marks located at the 
top of each column in the spreadsheet. DO NOT hard code maximum 
marks into the formula. 

So I need to know what the formula is that I should be using is?



Solution:

Make a sheet which will have Column headers in Row 1, 'Total Marks' of assignment in Row 2, Weight of each Assignment in Row 3, and Marks obtained in Row 4.
Have a look at the screenshot below:




Put the following formula in Cell D8:
=ROUND(((B4*B3)+(C4*C3)+(D4*D3)+(E4*E3)+(F4*F3))/((B2*B3)+(C2*C3)+(D2*D3)+(E2*E3)+(F2*F3))*100)


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,


1 comment:

  1. I have the same question as above. I used your solution but when i drag down the column to calculate the other 80+ grades i have to calculate the number are all messed up showing up above 100. When i use this method for each individual grade i have to calculate it works fine but doing so many will take me forever. Is there a way to just drag down and all the other grades will calculate properly?

    ReplyDelete