Excel formula: total points vs total points attempted

[amuswashere]

I'm working on a grading spreadsheet. Cells C3 thru M3 list the total points possible for each test. The rest of the spreadsheet are the students and the points for each test. I need to get an average for each student, but only for the tests they actually attempted. So basically a student didn't take the quiz with the point value in C3, then C3 value should be part of the average. Make sense.

Here is an example
Q1: 40 pts, Q2:40 pts, Q3: 5 pts, Q4: 25 pts
total possible points - 110
Johnnie has Q1: 0 pts, Q2: 40, Q3: 4, Q4: 20
Since Johnnie has a zero (didn't take the test) for Q1, I don't want that to be added in possible points attempted.

I will have three columns at the end: total points, total points attempted, then %

 

[OLineCoach]

Why place a zero for grade if he did not take it? Maybe just leave it blank, or put an "X" or something.

If you do this, an If-Then statement could be used saying that if an "X" was listed in a cell, then don't count it.

I teach too....Excel is a HUGE help in a lot of stuff. Good luck.

 

[amuswashere]

The student points field isn't really the problem. I just don't know how to get the average when each test is worth a different number of points. I was looking for an if,then expression so that if Johnnie's value for test two is 0, then I don't include test two's point values in the average. I'm working on this for my boss's wife, so it's the way she has it set up.

 

[Peter_SSs]

amuswashere

Welcome to the Mr Excel board!

Try this. You will have to modify the ranges to suit your data.

H4: =SUM(C4:G4)
I4: =SUMIF(C4:G4,">0",C$3:G$3)
J4 (formatted as percentage): =H4/I4
All three copied down

Mr Excel.xls
	B	C	D	E	F	G	H	I	J	K
2							Total	Attempted	%
3		40	40	50	25
4	Johnnie	0	40	4	20		64	115	55.65%
5	Max	20			12.5		32.5	65	50.00%
6
Grading

 

[amuswashere]

Thank you! Thank you! Thank you!

Thank you so much! That is exactly what I was looking for. YOU ROCK!!!