Ron said: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 an
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
:
Is there a way to average only the X highest number of scores in
row?
What about if there is an additional condition, i.e., if the column
heading says "Quiz", for instance, instead of "Test"?
XXL User said: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 said: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
:
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"?
Ron said:Can you post a sample table so we can see the structure you're dealin
with?
***********
Regards,
Ron
XL2002, WinXP
:
Thanks for you response; I realize I wasn't clear enough in m
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 formul
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 gradeshee
to
set their own minimum number of quiz scores to average. Is their
way
to put a variable determined by a number input in another cel
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 websit
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 quizze
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 yo
press
[Enter], instead of just pressing [Enter].
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
:
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 said: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 said:Can you post a sample table so we can see the structure you're dealing
with?
***********
Regards,
Ron
XL2002, WinXP
:
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
:
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"?
Ron said: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
:
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 b
counted,
preferably by entering that number in a cell found in a separat
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
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'r
dealing
with?
***********
Regards,
Ron
XL2002, WinXP
:
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
:
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 said: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 said: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
:
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
:
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
:
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"?
Ron Coderre said: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 said: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 said: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
:
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
:
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
:
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"?
Ron said: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
:
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
:
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 dow
[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
:
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 th
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
:
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 m
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
:
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"?