Averaging Values in Multiple Columns, with Conditions

[Lewis Clark]

I am a teacher creating a grade book in Excel. Each student is in a single row, with columns for the various assignments. For data analysis purposes, I am trying to develop formulas to make two different calculations.

The first calculation is simply the average quiz grade for all students, for all quizzes.

The second calculation is the average quiz grade for all grades that are higher than zero. This will tell me the average quiz score for quizzes that have actually been attempted.

I do need to track students that have withdrawn from the class, so column A will contain the letter "w" if the student has withdrawn from the class. If the student has withdrawn, the grades for that student should be ignored in the calculations.

Since I do accept quizzes after their due dates, a particular quiz score will have an entry of "L" if it has not yet been taken. These entries also need to be ignored in the calculations.

Please see the attached workbook for some sample data and the expected results.

Thank you very much in advance for any assistance!

[Bob Phillips]

Try these array formulae

=AVERAGE(IF((ISNUMBER($C$2:$E$6))*($A$2:$A$6<>"w"),$C$2:$E$6))
=AVERAGE(IF((ISNUMBER($C$2:$E$6))*($A$2:$A$6<>"w")*($C$2:$E$6<>0),$C$2:$E$6))

[ragulduy]

For the first one:
=SUM(IF(A2:A6="w",0,1)*IF(ISNUMBER(C2:E6),C2:E6,0))/SUM(IF(A2:A6="w",0,1)*IF(ISNUMBER(C2:E6),1,0))
for the second one:
=SUM(IF(A2:A6="w",0,1)*IF(ISNUMBER(C2:E6),C2:E6,0))/(SUM(IF(A2:A6="w",0,1)*IF(ISNUMBER(C2:E6),1,0))-COUNTIF(C2:E6,0))

Both are array formulas (need to be confirmed with ctrl+shift+enter)

[Lewis Clark]

Try these array formulae

=AVERAGE(IF((ISNUMBER($C$2:$E$6))*($A$2:$A$6<>"w"),$C$2:$E$6))
=AVERAGE(IF((ISNUMBER($C$2:$E$6))*($A$2:$A$6<>"w")*($C$2:$E$6<>0),$C$2:$E$6))

Thank you very much!

[Lewis Clark]

For the first one:
=SUM(IF(A2:A6="w",0,1)*IF(ISNUMBER(C2:E6),C2:E6,0))/SUM(IF(A2:A6="w",0,1)*IF(ISNUMBER(C2:E6),1,0))
for the second one:
=SUM(IF(A2:A6="w",0,1)*IF(ISNUMBER(C2:E6),C2:E6,0))/(SUM(IF(A2:A6="w",0,1)*IF(ISNUMBER(C2:E6),1,0))-COUNTIF(C2:E6,0))

Both are array formulas (need to be confirmed with ctrl+shift+enter)

Thank you very much!