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:
|
Originally Posted by Ron Coderre
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
|