Weighted multiplication formula that ignores zeroes

Hi all! I'm a teaching assistant drafting a grade spreadsheet for a college course. My goal is to have two columns that tabulate the student's overall grade: one as a running total of submitted assignments, so to speak, and the other as an absolute percentage of all work required. That way, in the middle of the term we could see that a student has a 90% based on assignments completed ("running total" column), even if he would get a 46% if he stopped right there and turned nothing more in ("absolute total" column).

jshot-155.jpg

I figured out the "all work" column, as you can see in the screen grab, but I can't figure out how to construct a "running total" formula. The assignments are weighted to different percentages of the grade, which is a bit daunting, but my main question is how to write a formula that calculates the weighted average among finished assignments (nonzero values) while ignoring those that aren't due yet (zero values). Any advice? Thanks so much!

-Cliff

How do you distinguish between different zero's? (aren't due yet, did not turn in assignment, got 0 in test)

Originally Posted by ckim551

How do you distinguish between different zero's? (aren't due yet, did not turn in assignment, got 0 in test)

Good question, and I think course policy accounts for it. In order to pass the class, all students must turn in the assignment, even after it's late enough to be an automatic F. All F's are recorded as 59, so any 0 can be ignored as either something that isn't due yet or something that is late (which would become a nonzero score once submitted).

I should be able to post the spreadsheet in a few hours!

Originally Posted by FDibbins

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Fair points! Hopefully this spreadsheet got through all right. Each pair of assignment columns is set up to convert a teaching assistant's letter grade into a number grade, except for the final exam, which converts number-->letter. The N Column is where I need the weighted formula that ignores zeroes.

200 TA Grade Spreadsheet Mockup.xlsx

Rows 3-5 show how I would like the N column to behave while a student's course grade is still in progress, while rows 7-9 show the end-of-term result, in which the "Current" and "Final" grades are finally the same. Rows 18-24 feature the same entries as dumb data, with all of the formulas stripped away.

As a sidebar, I would love to know how to get a formula cell (e.g., those in the I Column) to display as blank but still retain the formula--staring at a bunch of F's is distracting, and may be confusing for some of my fellow TAs!

Originally Posted by FDibbins

You have B+ there, but the score was 87.5, which is less than the 89 score required for B+?

First of all, thank you so much, FDibbins! I'm off to a great start implementing these, but I'm afraid I don't understand how/where to apply the INDEX formula.

200 TA Grade Spreadsheet Mockup 2.xlsx

As for your question about the 87.5, it raises another concern of mine. The while a letter grade always converts to the same number (a "B+" always equals 89), the exam, like the final grade tabulator, must judge anything within a certain range to be a given letter grade. So, anything between 86.5 and 89.49 equals a "B+". First of all, would there be a way to use a modified LOOKUP formula in cell I3 to detect the proper grade range for the manually input value in J3, and produce the appropriate letter grade? Or should I stick with the nested IF formula, which already produces that result? Second, getting back to my primary goal, how can I write the formula for N3 that creates a weighted average while ignoring zeroes?