Finding the average of 4 highest of 6 numbers

[Cornelius Morris]

I need to figure the average of the 4 highest of 6 quiz scores for a class that I am teaching. I drop the two lowest scores, but those are the two lowest submitted quizzes; a zero (= student skipped the quiz) does not count as one of the two dropped.

So: a sample student has these six cells:
P3 (83), Q3 (67), R3 (58), S3 (0), T3 (17), U3 (50).

I want to drop the 17 and the 50.

Thanks in advance for any suggestions.

Cornelius

 

[Scott Huish]

Perhaps:
=AVERAGE(LARGE((IF(P3:U3>0,P3:U3)),ROW(INDIRECT("1:"&4-COUNTIF(P3:U3,0)))))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[admiral100]

Another way :

=AVERAGE(IFERROR(LARGE(P3:U3,{1,2,3,4}-COUNTIF(P3:U3,"=0")),""))

Confirm with CTRL-SHIFT-ENTER

 

[barry houdini]

So: a sample student has these six cells:
P3 (83), Q3 (67), R3 (58), S3 (0), T3 (17), U3 (50).

I want to drop the 17 and the 50.

So what's the required result? If you aren't dropping the zero is that included in the average?

 

[Eric W]

I must have understood the question differently - I came up with

=(SUM(LARGE(IF(P3:U3=0,999,P3:U3),{1,2,3,4}))-COUNTIF(P3:U3,0)*999)/4
confirmed with Control+Shift+Enter.

It returns 52 = (83+67+58+0)/4

 

[barry houdini]

This non array formula should give you the same result as Eric's

=(SUM(P3:U3)-SUM(SMALL(P3:U3,{1,2}+MIN(4,COUNTIF(P3:U3,0)))))/4

 

[Cornelius Morris]

Thanks, everyone. I got the result with Eric's and Barry's versions.

Yes, I wasn't dropping the zero; the student gets the two lowest quizzes dropped, but gets no break if the quiz isn't submitted.

Cornelius