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

In Col. Y, I've calculated the number of contributions the students
has made.
In Col. Z, I've calculated the average for all the contributions the
student has
contributed.

Beside each number grade, I've put the letter grade in the next
column.
However, I'm getting an error message [#value!] with the forumula that
I'm using: =IF(Y19<8,Z19-(8-
Y19)*10,AVERAGE(IF(A19:X19=LARGE(A19:X19,8),A19:X 19)))

Help would be greatly appreciated.

Cheers
Rick

Let's see if I understand you correctly....

With
A1: Name
B1:M1 contains the series Week1, Week2....Week12
N1: Average
A2: (a student name)
B2: M2 contains test scores or blanks

For my example....test scores are whole numbers, not percents
(eg 100, 85, etc)

Name Week1 Week2 Week3....Week12
Student_A 80 (blank) 75
Student_B 90 87 92

Then this regular formula returns the average of up to 8 highest scores in
B2: M2 with 10-point penalties for each score count less than 8. Min score is
zero.

N2:
=MAX(AVERAGE(INDEX(LARGE(B2:M2+0,ROW($A$1:INDEX($A $1:$A$8,MIN(COUNT(C2:M2),8)))),0))-MAX(8-COUNT(B2:M2),0)*10,0)

Copy that formula down as far as you need

Note: The array formula version (committed with ctrl+shift+enter) is
N2:
=MAX(AVERAGE(LARGE(B2:M2+0,ROW($A$1:INDEX($A$1:$A$ 8,MIN(COUNT(C2:M2),8)))))-MAX(8-COUNT(B2:M2),0)*10,0)

Adjust range references to suit your situation

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"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 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.

In Col. Y, I've calculated the number of contributions the students
has made.
In Col. Z, I've calculated the average for all the contributions the
student has
contributed.

Beside each number grade, I've put the letter grade in the next
column.
However, I'm getting an error message [#value!] with the forumula that
I'm using: =IF(Y19<8,Z19-(8-
Y19)*10,AVERAGE(IF(A19:X19=LARGE(A19:X19,8),A19:X 19)))

Help would be greatly appreciated.

Cheers
Rick

In put my 10 makes in A1:L1
In M1, I used =SUM(A1:L1)
In N1, I used =COUNT(A1:L1)

In O1 I compute the average of the best 8 with
=(M1-(N111)*SMALL(A1:M1,4)-(N110)*SMALL(A1:M1,3)-(N19)*SMALL(A1:M1,2)-(N18)*SMALL(A1:M1,1))/8

In P1, I subtract 10 for every assignment less than 8 but limit lowest grade
to 0 with
=MAX(0,O1-10*(8-N1)*(N1<8))
Could round this with =MAX(0,ROUND(O1-10*(8-N1)*(N1<8),0))

Lots of scope for adding "EX" (excused) etc.
All this and no IF's !
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dr Traffic" wrote in message
ups.com...
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 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.

In Col. Y, I've calculated the number of contributions the students
has made.
In Col. Z, I've calculated the average for all the contributions the
student has
contributed.

Beside each number grade, I've put the letter grade in the next
column.
However, I'm getting an error message [#value!] with the forumula that
I'm using: =IF(Y19<8,Z19-(8-
Y19)*10,AVERAGE(IF(A19:X19=LARGE(A19:X19,8),A19:X 19)))

Help would be greatly appreciated.

Cheers
Rick

Do you realize that *your* formula is an *array* formula?

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Dr Traffic" wrote in message
ups.com...
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 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.

In Col. Y, I've calculated the number of contributions the students
has made.
In Col. Z, I've calculated the average for all the contributions the
student has
contributed.

Beside each number grade, I've put the letter grade in the next
column.
However, I'm getting an error message [#value!] with the forumula that
I'm using: =IF(Y19<8,Z19-(8-
Y19)*10,AVERAGE(IF(A19:X19=LARGE(A19:X19,8),A19:X 19)))

Help would be greatly appreciated.

Cheers
Rick

Yikes! Typos!
References to cell "C2" should be replace with references to cell "B2"

The regular formula should be:
N2:
=MAX(AVERAGE(INDEX(LARGE(B2:M2+0,ROW($A$1:INDEX($A $1:$A$8,MIN(COUNT(B2:M2),8)))),0))-MAX(8-COUNT(B2:M2),0)*10,0)

and the ARRAY FORMULA should be:
N2:
=MAX(AVERAGE(LARGE(B2:M2+0,ROW($A$1:INDEX($A$1:$A$ 8,MIN(COUNT(B2:M2),8)))))-MAX(8-COUNT(B2:M2),0)*10,0)



***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Let's see if I understand you correctly....

With
A1: Name
B1:M1 contains the series Week1, Week2....Week12
N1: Average
A2: (a student name)
B2: M2 contains test scores or blanks

For my example....test scores are whole numbers, not percents
(eg 100, 85, etc)

Name Week1 Week2 Week3....Week12
Student_A 80 (blank) 75
Student_B 90 87 92

Then this regular formula returns the average of up to 8 highest scores in
B2: M2 with 10-point penalties for each score count less than 8. Min score is
zero.

N2:
=MAX(AVERAGE(INDEX(LARGE(B2:M2+0,ROW($A$1:INDEX($A $1:$A$8,MIN(COUNT(C2:M2),8)))),0))-MAX(8-COUNT(B2:M2),0)*10,0)

Copy that formula down as far as you need

Note: The array formula version (committed with ctrl+shift+enter) is
N2:
=MAX(AVERAGE(LARGE(B2:M2+0,ROW($A$1:INDEX($A$1:$A$ 8,MIN(COUNT(C2:M2),8)))))-MAX(8-COUNT(B2:M2),0)*10,0)

Adjust range references to suit your situation

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"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 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.

In Col. Y, I've calculated the number of contributions the students
has made.
In Col. Z, I've calculated the average for all the contributions the
student has
contributed.

Beside each number grade, I've put the letter grade in the next
column.
However, I'm getting an error message [#value!] with the forumula that
I'm using: =IF(Y19<8,Z19-(8-
Y19)*10,AVERAGE(IF(A19:X19=LARGE(A19:X19,8),A19:X 19)))

Help would be greatly appreciated.

Cheers
Rick