0

I'm building my girlfriend a Google Sheets spreadsheet that will allow her to automatically compute her students' grades based on grades she gives her students on different projects, exams, and participation.

So for homework for example, we may want to give different weight to different assignments. Let's say these are our homeworks and their respective point values (which are then converted to percentages):

  • HW #1: 50
  • HW #2: 50
  • HW #3: 50
  • HW #4: 250

For this, I've successfully used SUMPRODUCT, which sums the multipiles of each students grade and the weight of that homework in %. The result is a weighted average for each student.

so for HW #1, if the student got 90% on this assignment, this value would be multiplied by 0.125.

For reference, this is how the formula looks for each student cell:

=IF(ISBLANK(participation!D14), "---", (sumproduct(participation!D14:Z14, participation!$D$11:$Z$11)))

Where D:14:Z14 in participation is the collection of grades for the different assignments. D11:Z11 is the collection of automatically calculated percentage for each assignment.

Where I'm stuck: What if I want to exempt a student? Say Bob didn't do HW #4 because he was sick, right now, as soon as I enter the weight for this assignment (250), this formula causes Bob's average to drop as long as I didn't input a grade for HW #4. How do I skip the multiplication if the grade value in the array is empty? (the weight value might be populated anyway)

Note: I'm an Excel n00b and have put this together mainly by doing research online and coppying snippets and semi-snippets online. I appreciate your patience.

Screenshot of the relevant cells that the formula references: enter image description here

Improve this question
5
  • Just curious, were none of the free templates for grade books of any value? They would have saved you a lot of time.
    – CharlieRB
    Commented Sep 10, 2015 at 12:55
  • Thanks for the suggestion Charlie, however the spreadsheet I'm building is in hebrew and modifying those spreadsheets would be very cumbersome. This is the last hurdle I'm facing.
    – zerohedge
    Commented Sep 10, 2015 at 12:56
  • That makes sense. As another user tried to post below, is there a way you can give us an example so we can attempt to help you. This question is long and difficult to follow.
    – CharlieRB
    Commented Sep 10, 2015 at 12:57
  • Will post a screenshot in a bit with some commentary.
    – zerohedge
    Commented Sep 10, 2015 at 12:57
  • I've added a screenshot @CharlieRB.
    – zerohedge
    Commented Sep 10, 2015 at 13:16

2 Answers 2

Reset to default
1

For your specific case, your formula on your generalsheet in cell C10 should be:

=IF(SUM(participation!D14:Z14)=0,"",SUMPRODUCT(participation!D14:Z14,participation!D11:Z11)/(SUMIF(participation!D14:Z14,"<>",participation!D11:Z11)-SUMIF(participation!D14:Z14,"=E",participation!D11:Z11)))

I've messed around with your file to understand things a little bit better. The only part which should be of interest to you is on the generalsheet cell C10, which should now do what you were asking for!

It's basically the formula of your previous example, just adjusted to your needs.

Improve this answer
9
  • I've only just now seen this and pasted the formula into the first cell, it appears to be working perfectly!! Many many thanks for sticking up with this and for the time and effort you've put into helping me! I'll now try to understand the syntax and logic so I can maybe solve problems like this one on my own in the future. (P.S.: If you can bothered to delete/unshare the sheet it would be great, it contains some private information currently.)
    – zerohedge
    Commented Sep 12, 2015 at 0:16
  • 1
    I've removed it.
    – A1985
    Commented Sep 12, 2015 at 16:25
  • Link still works for me as of now.
    – zerohedge
    Commented Sep 12, 2015 at 17:59
  • I have removed the link from my answer. Have removed it from my gdrive earlier today, so I guess that's because of your cache. On my mobile it's not available anymore.
    – A1985
    Commented Sep 12, 2015 at 19:07
  • I still see it on multiple browsers and after clearing cache (incognito) but not going to bother you with this thing now. Thanks a bunch once again!
    – zerohedge
    Commented Sep 12, 2015 at 19:30
1

Even with your screenshot I'm not 100% sure if I did understand your needs.

What I believe you are looking for is:

You've got several students, homeworks and points on those homeworks which your girlfriend students will reach a percentage of. So if they do their homework perfectly fine they will get 100% of those points, if they didn't make them at all it's going to be 0%.

So this is what I did:

enter image description here

Both, SUMPRODUCT as well as AVERAGE ignore the empty field. Meaning that the average percentage (line 12) won't drop. However it will drop, if you enter 0% in D10.

Please note: D2:D10 are formated as Percentage!

Here's the result to my example.

enter image description here

I hope this helps.

Edit What I don't really understand is, what you need the sumproduct for. If you care about the points and not only about the percentage, how many points would you give him if he's sick? 300 Points seem to be wrong, as he didn't do anything...

I would only go by the percentage and drop the points per student. And the average percentage you can achive using the average formula.

https://support.office.com/en-nz/article/AVERAGE-function-047bac88-d466-426c-a32b-8f33eb960cf6?ui=en-US&rs=en-NZ&ad=NZ

Improve this answer
6
  • Andre, here's what I need the points for: 1) to be able to dynamically set a percentage for each homework, while keeping the average relative to the total number of points. So for example if I have 10p, 10p, 10p, and I add a 20p HW then this affect the percentage given for each other HW. Going by percentage only isn't sufficient here. 2) The points aren't given per student, they are given per assignment. 3) there are other categories for which students are graded, so I need to get an average of all those values.
    – zerohedge
    Commented Sep 10, 2015 at 14:09
  • See example here: the first one uses a similar system, but within one sheet, I have each category in different sheets: vertex42.com/ExcelTemplates/gradebook.html
    – zerohedge
    Commented Sep 10, 2015 at 14:10
  • Right now with my formula the student's grade for homeworks IS dropping even though I kept the cell under one of the 38.5% empty. If I input any other number bigger than zero there then the average goes up.
    – zerohedge
    Commented Sep 10, 2015 at 14:13
  • I think it would be much easier to understand what you need, if you would provide your file. Your example file does exactly what I understand from your needs.
    – A1985
    Commented Sep 10, 2015 at 14:20
  • 1
    Uff, now I see why you didn't upload it :) I need to translate everything first in order to understand it. But I think I'll get this done... Might take some time, though...
    – A1985
    Commented Sep 10, 2015 at 15:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .