Average top 10 or bottom 10
 

Is there a way to get an average of the top 10 records (or bottom 10) in a
column of numbers (sometimes formatted as $'s sometimes as %'s), other than
sorting and counting ten cells?
The cells are already defined in the function MAX(A1:A220) and I was hoping
to just replace the "MAX" with the appropriate function(s).

Thanks!

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

bottom 10

=SUMPRODUCT(small(A1:A100,{1,2,3,4,5,6,7,8,9,10}))/10

"LRS" wrote in message
...
Is there a way to get an average of the top 10 records (or bottom 10) in a
column of numbers (sometimes formatted as $'s sometimes as %'s), other
than
sorting and counting ten cells?
The cells are already defined in the function MAX(A1:A220) and I was
hoping
to just replace the "MAX" with the appropriate function(s).

Thanks!

On Wed, 25 May 2005 10:54:09 -0700, LRS wrote:

Is there a way to get an average of the top 10 records (or bottom 10) in a
column of numbers (sometimes formatted as $'s sometimes as %'s), other than
sorting and counting ten cells?
The cells are already defined in the function MAX(A1:A220) and I was hoping
to just replace the "MAX" with the appropriate function(s).

Thanks!

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


--ron

Thanks for such quick responses!

"Ron Rosenfeld" wrote:

On Wed, 25 May 2005 10:54:09 -0700, LRS wrote:

Is there a way to get an average of the top 10 records (or bottom 10) in a
column of numbers (sometimes formatted as $'s sometimes as %'s), other than
sorting and counting ten cells?
The cells are already defined in the function MAX(A1:A220) and I was hoping
to just replace the "MAX" with the appropriate function(s).

Thanks!

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


--ron