Average top 10 or bottom 10

G

Guest

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!
 
N

N Harkawat

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

Ron Rosenfeld

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!
Click to expand...

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


--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter by Top 10 doesn't work 1
Top 10% and Bottom 10% 2
Average Unique 7
Average If... 12
Average weekly maximum 2
How to remove the top & bottom 10% from a list? 3
filter data by top 10 AND bottom 10 items simultaneously 4
Excel base 10 log for Optical Density vs %T 1

Top