Average of Numbers in Top 5

[Guest]

How do I tell excel to look at only the top x numbers in a list of values?
For example, if I have 100 scores and I want Excel to calculate the average
of the top 10 scores, how do I do that?

Thanks

 

[Guest]

One way

=AVERAGE(LARGE($A$1:$A$100,ROW(INDIRECT("1:10"))))

entered with ctrl + shift & enter

regards,

Peo Sjoblom

 

[Bob Phillips]

=AVERAGE(LARGE(A1:A100,{1,2,3,4,5,6,7,8,9,10}))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks, now what if I want the number (10 in this case) to be variable based

 

[Guest]

Thanks but this doesn't seem to be working for me. Also, I'd like to be able
to vary the number of top scores included based on a referenced cell.

 

[Bob Phillips]

=AVERAGE(LARGE(A1:A20,ROW(INDIRECT("1:"&B1))))

where B1 is the referenced cell. It also becomes an array formula, so commit
with Ctrl-Shift-Enter, not just Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

=AVERAGE(LARGE(A1:A100,ROW(INDIRECT("1:"&E1))))

Array-entered, where E1 holds the nth largest value to include in the average.

HTH
Jason
Atlanta, GA

 

[Guest]

Thanks!

=AVERAGE(LARGE(A1:A20,ROW(INDIRECT("1:"&B1))))

where B1 is the referenced cell. It also becomes an array formula, so commit
with Ctrl-Shift-Enter, not just Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)