Average of Numbers in Top 5

J

Jake

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
 
P

Peo Sjoblom

One way

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

entered with ctrl + shift & enter

regards,

Peo Sjoblom
 
B

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)
 
J

Jake

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.
 
B

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)
 
J

Jason Morin

=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
 
J

Jake

Thanks!

Bob Phillips said:
=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)
Click to expand...
 
Top