Formula Help

[Dr Traffic]

Hi There:

I have a sheet of student marks which potentially run across 12 weeks.
Each students needs to make a mandatory 8 contributions. If the
student makes 8 contributions, then I simply average the marks to come
up with a grade. Some of the columns have blank weeks where the
student does not make a contribution. I've arranged the students names
in columns and the marks are in rows.

If the student makes less than 8 contributions, than s/he will lose 10
marks from their average for every contribution s/he is below 8. For
example, if s/he makes 6 contributions and the 6 marks average 80%,
than s/he will only receive 60%.

If the student makes more than 8 contributions--and this is the part of
the formula that I'm having trouble with--than I deduct the lowest
marks. Therefore, if the student makes 12 contributions, than I deduct
the 4 lowest marks before calculating the average. For example, if the
student receives the following marks: 78, 56, 95, 100, 61, 84, 77, 73,
94, 82, 71, 88. Then I omit 56, 61, 71 & 73 before calculating the
average.

=IF(P4<8,SUM((Q4-(8-P4)*10))),if P4>8 - This is the formula that I have
thus far. In the P column I've calculated the number of contributions
with the formula: =COUNTIF(D4:O4,">1")

I am still a novice when it comes to formulas, so if this nascent work
could be done a better way, I am open to suggestions.

Help would be greatly appreciated.

Cheers
Rick

 

[JLatham]

I believe pinmaster has provided a solution in your post of same request in
the GQ section.