Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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"?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
|
|||
|
|||
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? Quote:
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
|
|||
|
|||
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. Quote:
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
|
|||
|
|||
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. Quote:
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
|
|||
|
|||
Yep, it works great. Thanks for all your help, Ron.
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
round up numbers to next highest "4" | Excel Worksheet Functions | |||
Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
How do I add only the highest three numbers in a range of data? | Excel Worksheet Functions | |||
How to add 4 of 5 highest numbers using a formula | Excel Discussion (Misc queries) |