Gradebook question

[joe_pilgrim]

Hey guys!

Before anyone begins to bash me about my question, I'd like to offer any apology as I have searched on the web and including this site to no avail.

I have been using my own gradebook to keep track of my grades at school but each semester, I'm always faced with not know what my current grade or real time status. I would suppose it depends on the type of formula I am using because the only time it actually serves its purpose is right before FINALS as it is the last grade for the semester.

In any event, I would like to share the following:

For each class, I have broken down the grade distribution based on the syllabus. (ie, Homework - 10%, Midterm1 - 20%, Midterm2 - 30%, and Final Exam - 40%)
My issue is that after every homework or midterm I have taken or received a grade, I can not see or know my current grade status since the semester is not completed or reached 100%.
My current formula reads as follows, where I am summing up all my 'Actual' points and dividing them by all my 'Possible' points and then multiplying it to it's respective weigh:
=(B21/C21*B3)+(D21/E21*D3)+(F21/G21*F3)+(H21/I21*H3)

Is this not the proper formula that I am to use based on what I am attempting to do? Or do I have to modify something and what is that something? Please advise and thank you for assisting me with this matter.

Regards,

JP

 

[syntaxed]

I'm assuming columns B and C are for Homework, D and E for Midterm1, F and G for Midterm2, and H and I for Final Exam, B3, D3, F3, H3 contain the weightages 10%, 20%, 30% and 40%, and that B21, D21, F21, H21 contain the scores you've obtained so far that semester, with C21, E21, G21, I21 being the max. scores attainable.

Mid-way through the semester what value do you enter in cell H21 and I21? Zeroes in both?

 

[joe_pilgrim]

@syntaxed thanks for the prompt reply! To answer your question, they are left blank or currently do not contain any data as I only enter data once I have received a score.

 

[syntaxed]

If you're using Excel 2007, you could replace your formula with this:

=IFERROR(B5/C5,0)*$B$3*SUM($B$3,$D$3,$F$3,$H$3)/SUM($B$3*AND(C5>0),$D$3*AND(E5>0),$F$3*AND(G5>0),$H$3*AND(I5>0))+IFERROR(D5/E5,0)*$D$3*SUM($B$3,$D$3,$F$3,$H$3)/SUM($B$3*AND(C5>0),$D$3*AND(E5>0),$F$3*AND(G5>0),$H$3*AND(I5>0))+IFERROR(F5/G5,0)*$F$3*SUM($B$3,$D$3,$F$3,$H$3)/SUM($B$3*AND(C5>0),$D$3*AND(E5>0),$F$3*AND(G5>0),$H$3*AND(I5>0))+IFERROR(H5/I5,0)*$H$3*SUM($B$3,$D$3,$F$3,$H$3)/SUM($B$3*AND(C5>0),$D$3*AND(E5>0),$F$3*AND(G5>0),$H$3*AND(I5>0))

Sorry it looks like such a mess. You could use the Name manager to clean it up.

 

[syntaxed]

For your example, i.e. Row 21, the 5's would be 21's, so the formula would be:

=IFERROR(B21/C21,0)*$B$3*SUM($B$3,$D$3,$F$3,$H$3)/SUM($B$3*AND(C21>0),$D$3*AND(E21>0),$F$3*AND(G21>0),$H$3*AND(I21>0))+IFERROR(D21/E21,0)*$D$3*SUM($B$3,$D$3,$F$3,$H$3)/SUM($B$3*AND(C21>0),$D$3*AND(E21>0),$F$3*AND(G21>0),$H$3*AND(I21>0))+IFERROR(F21/G21,0)*$F$3*SUM($B$3,$D$3,$F$3,$H$3)/SUM($B$3*AND(C21>0),$D$3*AND(E21>0),$F$3*AND(G21>0),$H$3*AND(I21>0))+IFERROR(H21/I21,0)*$H$3*SUM($B$3,$D$3,$F$3,$H$3)/SUM($B$3*AND(C21>0),$D$3*AND(E21>0),$F$3*AND(G21>0),$H$3*AND(I21>0))

 

[joe_pilgrim]

@syntaxed it worked! Thank you for your assistance! I could have never figured it out

 

[syntaxed]

So glad it worked

For future reference (in case you need to change it at some point), what the formula is doing now is looking at what assessments you have taken so far and adjusting the weights accordingly. For instance if the final exam has not been taken yet, it changes the weights of Homework, Midterm1 and Midterm2 from 10%, 20%, 30% to 16.67%, 33.33% and 50% respectively, thus enabling you to track your progress during the year too, relative to the assessments available till date.