Korean Job Discussion Forums

[pkang0202]

I have this gradebook, but I need help with the formulas.





____A_________B_______C_______D________E (Avg)


1 John ________7_______ ________12


2 Mike ________9 _______5________


3 Sally _______13_______11 _______15


4 Possible pts: 15 _______12_______15



So, here's the problem. I need a formula to calculate the average (E). However, I must ignore the blanks. The formula must also ignore the "Out of" if there is a blank.

For example: John was not there for Quiz 2 so his C column is blank. When I calculate his grade, have to pretend like Quiz 2 didn't exist for him. So John's average grade would be 19/30 = 63.

Whereas Mike didn't do Quiz 3. So his average will be 14/27= 52.


Can anyone help me with the formulation. The formulas I found don't account for varying possible points. I found formulas to ignore blanks but I don't know how to get it to ignore the blank AND ignore the points possible column to.

The logic would be:

if (cell = blank)
Average grades in row ignoring possible points of the column with blank cell

[mehamrick]

could you just not put a zero in there instead of leaving it blank?

I am curious about setting up something like this as well. Let me know how it goes.

[mehamrick]

sorry for the double post.. try this site I found with google..

http://tlt.psu.edu/suggestions/gradebook/formulas.html

[the foystein]

Change the scores to a percentage - then the average is easy.

[Fresh Prince]

=AVERAGE(IF(A2:A7<>0, A2:A7,""))

This should average everything in the range unless the value is zero.

[pkang0202]

[the foystein]

Ok, then there is an easy solution. Add a column next to the raw score and put in the formula to make the raw score a percentage.

[Scouse Mouse]

Easy solution 1: Put a column in there for total tests taken and then divide by that

Easy solution 2: Put a column next to each test and add a 1 if they take the test, or a 0 if they do not. Total these at the end and divide.

[pkang0202]

The problem I'm running into is doing the calculation for Row 4.

How can I tell the formula for Average in Column E to say, "Yo, John didn't take the 2nd test. So, in Row 4, don't calculate add the 12 to the total possible points"

And in the same formula, it's gotta say, "yo, Mike didn't take the 3rd test. So don't calculate add 15 to the total possible points"

I need a formula that says something like:

if (row, column) == blank, then subtract (4 , column) from Total Possible points.

[mehamrick]