Student Marks Calculation

[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 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:X19)))

Help would be greatly appreciated.

Cheers
Rick

 

[Ron Coderre]

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

 

[Bernard Liengme]

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-(N1>11)*SMALL(A1:M1,4)-(N1>10)*SMALL(A1:M1,3)-(N1>9)*SMALL(A1:M1,2)-(N1>8)*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

 

[Ragdyer]

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

 

[Ron Coderre]

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

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

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 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:X19)))

Help would be greatly appreciated.

Cheers
Rick