Averaging Only the X Highest Numbers in a Row
 

Is there a way to average only the X highest number of scores in a row? What about if there is an additional condition, i.e., if the column heading says "Quiz", for instance, instead of "Test"?

Averaging Only the X Highest Numbers in a Row
 

Try something like this, using ARRAY FORMULAS*:

With
A table of scores in A1:K2

Where
A1: Name
B1:K1 (containing either TEST or QUIZ)
A2: (a name)
B2:K2 (containing scores)

These formulas return the average of the top 3 scores for quizzes and tests,
resp.
L1: QAvg
L2: =AVERAGE(LARGE(IF(B1:K1="QUIZ",B2:K2),{1,2,3}))

M1: TAvg
M2: =AVERAGE(LARGE(IF(B1:K1="TEST",B2:K2),{1,2,3}))

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].


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

XL2002, WinXP


"XXL User" wrote:


Is there a way to average only the X highest number of scores in a row?
What about if there is an additional condition, i.e., if the column
heading says "Quiz", for instance, instead of "Test"?


--
XXL User

Thanks for you response; I realize I wasn't clear enough in my question, though. I have two problems remaining:

1. Below B1, above the scores, is row C1, where teachers enter the original total of the quiz, which varies. I've tried your formula using SUM instead of average, and it works, but I will still need to take these scores and divide them by the quiz max in column C, giving a weighted average of the highest x quiz scores.

2. Problem two is that I would have liked the users of my gradesheet to set their own minimum number of quiz scores to average. Is their a way to put a variable determined by a number input in another cell instead of the {1,2,3} in your formula?

I really appreciate your help. Btw, how do you guys figure out this stuff? Excel help doesn't seem enough. Is there some website reference or book I can go back to without troubling the forum all the time?

Averaging Only the X Highest Numbers in a Row
 

Can you post a sample table so we can see the structure you're dealing with?

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

XL2002, WinXP


"XXL User" wrote:


Thanks for you response; I realize I wasn't clear enough in my question,
though. I have two problems remaining:

1. Below B1, above the scores, is row C1, where teachers enter the
original total of the quiz, which varies. I've tried your formula using
SUM instead of average, and it works, but I will still need to take
these scores and divide them by the quiz max in column C, giving a
weighted average of the highest x quiz scores.

2. Problem two is that I would have liked the users of my gradesheet to
set their own minimum number of quiz scores to average. Is their a way
to put a variable determined by a number input in another cell instead
of the {1,2,3} in your formula?

I really appreciate your help. Btw, how do you guys figure out this
stuff? Excel help doesn't seem enough. Is there some website reference
or book I can go back to without troubling the forum all the time?

Ron Coderre Wrote:
Try something like this, using ARRAY FORMULAS*:

With
A table of scores in A1:K2

Where
A1: Name
B1:K1 (containing either TEST or QUIZ)
A2: (a name)
B2:K2 (containing scores)

These formulas return the average of the top 3 scores for quizzes and
tests,
resp.
L1: QAvg
L2: =AVERAGE(LARGE(IF(B1:K1="QUIZ",B2:K2),{1,2,3}))

M1: TAvg
M2: =AVERAGE(LARGE(IF(B1:K1="TEST",B2:K2),{1,2,3}))

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].


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

XL2002, WinXP


"XXL User" wrote:


Is there a way to average only the X highest number of scores in a
row?
What about if there is an additional condition, i.e., if the column
heading says "Quiz", for instance, instead of "Test"?


--
XXL User



--
XXL User

Ok:

(I couldn't get the columns to be spaced out--sorry)
C D E F G H I J K
1 Type Quiz Test Quiz Essay Test Quiz Quiz Quiz ...
2 Total Pts 10 15 5 20 25 8 10 5
3 John 8 14 4 18 23 4 9 5
4 Ray 6 11 5 13 15 4 3 1

In this gradebook, which is to be used by several departments, each department is to be able to determine how many quizzes will be counted, preferably by entering that number in a cell found in a separate setup worksheet tab.

Once that number is set, say 4, I'd like the program to produce the average of the 4 highest quiz scores for each student (prob in the A or B column), with each score to be included in the average first calculated by dividing the student score by total pts.

So in John's case, the four highest scores that would be averaged a (k3/k2 + j3/j2 + d3/d2 + f3/f2), while in Ray's (f4/f2 + d4/d2 + i4/i2 + j4/j2).

.... while another department might choose to count the 3 highest, or 5, etc.

Thanks.

Averaging Only the X Highest Numbers in a Row
 

XXL User

I think I may have a solution this time....

Using your data table in C1:K3

A1: (the number of highest quiz scores to use)

B3:
=SUMPRODUCT(LARGE(IF(D$1:K$1="QUIZ",D3:K3/D$2:K$2,0),ROW($A$1:INDEX($A:$A,$A$1))))

Note: That is an ARRAY FORMULA. For array formulas, hold down [Ctrl] and
[Shift] when you press [Enter], instead of just pressing [Enter].

Copy B3 down into B4 and down as far as you need

Using the sample data
B3 returns 3.5
B4 returns 2.4

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"XXL User" wrote:


Ok:

(I couldn't get the columns to be spaced out--sorry)
C D E F G H I
J K
1 Type Quiz Test Quiz Essay Test Quiz Quiz
Quiz ...
2 Total Pts 10 15 5 20 25 8 10
5
3 John 8 14 4 18 23 4 9
5
4 Ray 6 11 5 13 15 4 3
1

In this gradebook, which is to be used by several departments, each
department is to be able to determine how many quizzes will be counted,
preferably by entering that number in a cell found in a separate setup
worksheet tab.

Once that number is set, say 4, I'd like the program to produce the
average of the 4 highest quiz scores for each student (prob in the A or
B column), with each score to be included in the average first
calculated by dividing the student score by total pts.

So in John's case, the four highest scores that would be averaged a
(k3/k2 + j3/j2 + d3/d2 + f3/f2), while in Ray's (f4/f2 + d4/d2 + i4/i2
+ j4/j2).

.... while another department might choose to count the 3 highest, or
5, etc.

Thanks.

Ron Coderre Wrote:
Can you post a sample table so we can see the structure you're dealing
with?

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

XL2002, WinXP


"XXL User" wrote:


Thanks for you response; I realize I wasn't clear enough in my
question,
though. I have two problems remaining:

1. Below B1, above the scores, is row C1, where teachers enter the
original total of the quiz, which varies. I've tried your formula
using
SUM instead of average, and it works, but I will still need to take
these scores and divide them by the quiz max in column C, giving a
weighted average of the highest x quiz scores.

2. Problem two is that I would have liked the users of my gradesheet
to
set their own minimum number of quiz scores to average. Is their a
way
to put a variable determined by a number input in another cell
instead
of the {1,2,3} in your formula?

I really appreciate your help. Btw, how do you guys figure out this
stuff? Excel help doesn't seem enough. Is there some website
reference
or book I can go back to without troubling the forum all the time?

Ron Coderre Wrote:
Try something like this, using ARRAY FORMULAS*:

With
A table of scores in A1:K2

Where
A1: Name
B1:K1 (containing either TEST or QUIZ)
A2: (a name)
B2:K2 (containing scores)

These formulas return the average of the top 3 scores for quizzes
and
tests,
resp.
L1: QAvg
L2: =AVERAGE(LARGE(IF(B1:K1="QUIZ",B2:K2),{1,2,3}))

M1: TAvg
M2: =AVERAGE(LARGE(IF(B1:K1="TEST",B2:K2),{1,2,3}))

*Note: For array formulas, hold down [Ctrl] and [Shift] when you
press
[Enter], instead of just pressing [Enter].


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

XL2002, WinXP


"XXL User" wrote:


Is there a way to average only the X highest number of scores in a
row?
What about if there is an additional condition, i.e., if the column
heading says "Quiz", for instance, instead of "Test"?


--
XXL User



--
XXL User



--
XXL User

That's great! Now is there any way to avoid errors. In particular, div!/0 in cases when a total was not input, or Value! when text, etc. is input in the student scores row?

Thanks for all your help.

Averaging Only the X Highest Numbers in a Row
 

See if this ARRAY FORMULA works for you

B3:
=SUMPRODUCT(LARGE(IF(ISNUMBER($D$2:$K$2)*ISNUMBER( D3:K3)*(D$1:K$1="QUIZ"),D3:K3/D$2:K$2,0),ROW($A$1:INDEX($A:$A,$A$1))))

Remember to commit with ctrl+shift+enter

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"XXL User" wrote:


That's great! Now is there any way to avoid errors. In particular,
div!/0 in cases when a total was not input, or Value! when text, etc.
is input in the student scores row?

Thanks for all your help.


Ron Coderre Wrote:
XXL User

I think I may have a solution this time....

Using your data table in C1:K3

A1: (the number of highest quiz scores to use)

B3:
=SUMPRODUCT(LARGE(IF(D$1:K$1="QUIZ",D3:K3/D$2:K$2,0),ROW($A$1:INDEX($A:$A,$A$1))))

Note: That is an ARRAY FORMULA. For array formulas, hold down [Ctrl]
and
[Shift] when you press [Enter], instead of just pressing [Enter].

Copy B3 down into B4 and down as far as you need

Using the sample data
B3 returns 3.5
B4 returns 2.4

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"XXL User" wrote:


Ok:

(I couldn't get the columns to be spaced out--sorry)
C D E F G H I
J K
1 Type Quiz Test Quiz Essay Test Quiz Quiz
Quiz ...
2 Total Pts 10 15 5 20 25 8 10
5
3 John 8 14 4 18 23 4
9
5
4 Ray 6 11 5 13 15 4
3
1

In this gradebook, which is to be used by several departments, each
department is to be able to determine how many quizzes will be
counted,
preferably by entering that number in a cell found in a separate
setup
worksheet tab.

Once that number is set, say 4, I'd like the program to produce the
average of the 4 highest quiz scores for each student (prob in the A
or
B column), with each score to be included in the average first
calculated by dividing the student score by total pts.

So in John's case, the four highest scores that would be averaged a
(k3/k2 + j3/j2 + d3/d2 + f3/f2), while in Ray's (f4/f2 + d4/d2 +
i4/i2
+ j4/j2).

.... while another department might choose to count the 3 highest, or
5, etc.

Thanks.

Ron Coderre Wrote:
Can you post a sample table so we can see the structure you're
dealing
with?

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

XL2002, WinXP


"XXL User" wrote:


Thanks for you response; I realize I wasn't clear enough in my
question,
though. I have two problems remaining:

1. Below B1, above the scores, is row C1, where teachers enter the
original total of the quiz, which varies. I've tried your formula
using
SUM instead of average, and it works, but I will still need to take
these scores and divide them by the quiz max in column C, giving a
weighted average of the highest x quiz scores.

2. Problem two is that I would have liked the users of my gradesheet
to
set their own minimum number of quiz scores to average. Is their a
way
to put a variable determined by a number input in another cell
instead
of the {1,2,3} in your formula?

I really appreciate your help. Btw, how do you guys figure out this
stuff? Excel help doesn't seem enough. Is there some website
reference
or book I can go back to without troubling the forum all the time?

Ron Coderre Wrote:
Try something like this, using ARRAY FORMULAS*:

With
A table of scores in A1:K2

Where
A1: Name
B1:K1 (containing either TEST or QUIZ)
A2: (a name)
B2:K2 (containing scores)

These formulas return the average of the top 3 scores for quizzes
and
tests,
resp.
L1: QAvg
L2: =AVERAGE(LARGE(IF(B1:K1="QUIZ",B2:K2),{1,2,3}))

M1: TAvg
M2: =AVERAGE(LARGE(IF(B1:K1="TEST",B2:K2),{1,2,3}))

*Note: For array formulas, hold down [Ctrl] and [Shift] when you
press
[Enter], instead of just pressing [Enter].


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

XL2002, WinXP


"XXL User" wrote:


Is there a way to average only the X highest number of scores in a
row?
What about if there is an additional condition, i.e., if the column
heading says "Quiz", for instance, instead of "Test"?


--
XXL User



--
XXL User



--
XXL User



--
XXL User

Averaging Only the X Highest Numbers in a Row
 

This is a bit shorter:


B3:
=SUMPRODUCT(LARGE(IF(ISNUMBER(D3:K3/D$2:K$2)*(D$1:K$1="QUIZ"),D3:K3/D$2:K$2,0),ROW($A$1:INDEX($A:$A,$A$1))))

(remember: C+S+E)

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

XL2002, WinXP


"Ron Coderre" wrote:

See if this ARRAY FORMULA works for you

B3:
=SUMPRODUCT(LARGE(IF(ISNUMBER($D$2:$K$2)*ISNUMBER( D3:K3)*(D$1:K$1="QUIZ"),D3:K3/D$2:K$2,0),ROW($A$1:INDEX($A:$A,$A$1))))

Remember to commit with ctrl+shift+enter

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"XXL User" wrote:


That's great! Now is there any way to avoid errors. In particular,
div!/0 in cases when a total was not input, or Value! when text, etc.
is input in the student scores row?

Thanks for all your help.


Ron Coderre Wrote:
XXL User

I think I may have a solution this time....

Using your data table in C1:K3

A1: (the number of highest quiz scores to use)

B3:
=SUMPRODUCT(LARGE(IF(D$1:K$1="QUIZ",D3:K3/D$2:K$2,0),ROW($A$1:INDEX($A:$A,$A$1))))

Note: That is an ARRAY FORMULA. For array formulas, hold down [Ctrl]
and
[Shift] when you press [Enter], instead of just pressing [Enter].

Copy B3 down into B4 and down as far as you need

Using the sample data
B3 returns 3.5
B4 returns 2.4

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"XXL User" wrote:


Ok:

(I couldn't get the columns to be spaced out--sorry)
C D E F G H I
J K
1 Type Quiz Test Quiz Essay Test Quiz Quiz
Quiz ...
2 Total Pts 10 15 5 20 25 8 10
5
3 John 8 14 4 18 23 4
9
5
4 Ray 6 11 5 13 15 4
3
1

In this gradebook, which is to be used by several departments, each
department is to be able to determine how many quizzes will be
counted,
preferably by entering that number in a cell found in a separate
setup
worksheet tab.

Once that number is set, say 4, I'd like the program to produce the
average of the 4 highest quiz scores for each student (prob in the A
or
B column), with each score to be included in the average first
calculated by dividing the student score by total pts.

So in John's case, the four highest scores that would be averaged a
(k3/k2 + j3/j2 + d3/d2 + f3/f2), while in Ray's (f4/f2 + d4/d2 +
i4/i2
+ j4/j2).

.... while another department might choose to count the 3 highest, or
5, etc.

Thanks.

Ron Coderre Wrote:
Can you post a sample table so we can see the structure you're
dealing
with?

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

XL2002, WinXP


"XXL User" wrote:


Thanks for you response; I realize I wasn't clear enough in my
question,
though. I have two problems remaining:

1. Below B1, above the scores, is row C1, where teachers enter the
original total of the quiz, which varies. I've tried your formula
using
SUM instead of average, and it works, but I will still need to take
these scores and divide them by the quiz max in column C, giving a
weighted average of the highest x quiz scores.

2. Problem two is that I would have liked the users of my gradesheet
to
set their own minimum number of quiz scores to average. Is their a
way
to put a variable determined by a number input in another cell
instead
of the {1,2,3} in your formula?

I really appreciate your help. Btw, how do you guys figure out this
stuff? Excel help doesn't seem enough. Is there some website
reference
or book I can go back to without troubling the forum all the time?

Ron Coderre Wrote:
Try something like this, using ARRAY FORMULAS*:

With
A table of scores in A1:K2

Where
A1: Name
B1:K1 (containing either TEST or QUIZ)
A2: (a name)
B2:K2 (containing scores)

These formulas return the average of the top 3 scores for quizzes
and
tests,
resp.
L1: QAvg
L2: =AVERAGE(LARGE(IF(B1:K1="QUIZ",B2:K2),{1,2,3}))

M1: TAvg
M2: =AVERAGE(LARGE(IF(B1:K1="TEST",B2:K2),{1,2,3}))

*Note: For array formulas, hold down [Ctrl] and [Shift] when you
press
[Enter], instead of just pressing [Enter].


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

XL2002, WinXP


"XXL User" wrote:


Is there a way to average only the X highest number of scores in a
row?
What about if there is an additional condition, i.e., if the column
heading says "Quiz", for instance, instead of "Test"?


--
XXL User



--
XXL User



--
XXL User



--
XXL User

Yep, it works great. Thanks for all your help, Ron.