Formula Help
 

Hi The

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 P48 - 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

Formula Help
 

Hi,

try this:

=IF(P4<8,Q4-(8-P4)*10,AVERAGE(IF(D4:O4=LARGE(D4:O4,8),D4:O4)))
enter with CTRL+SHIFT+ENTER

HTH
Jean-Guy

"Dr Traffic" wrote:

Hi The

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 P48 - 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