Average of Numbers in Top 5
 

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

One way

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

entered with ctrl + shift & enter

regards,

Peo Sjoblom

"Jake" wrote:

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

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


"Jake" wrote in message
...
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

Thanks, now what if I want the number (10 in this case) to be variable based
on a referenced cell?

"Bob Phillips" wrote:

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


"Jake" wrote in message
...
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

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.

"Peo Sjoblom" wrote:

One way

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

entered with ctrl + shift & enter

regards,

Peo Sjoblom

"Jake" wrote:

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

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


"Jake" wrote in message
...
Thanks, now what if I want the number (10 in this case) to be variable
based
on a referenced cell?

"Bob Phillips" wrote:

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


"Jake" wrote in message
...
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

=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

"Jake" wrote:

Thanks, now what if I want the number (10 in this case) to be variable based
on a referenced cell?

"Bob Phillips" wrote:

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


"Jake" wrote in message
...
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

Thanks!

"Bob Phillips" wrote:

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


"Jake" wrote in message
...
Thanks, now what if I want the number (10 in this case) to be variable
based
on a referenced cell?

"Bob Phillips" wrote:

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


"Jake" wrote in message
...
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