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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]> 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 <[email protected]> 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
>