Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
=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! |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average function in Excel 2002 | New Users to Excel | |||
MIN, MAX, AVERAGE functions | Excel Worksheet Functions | |||
Average the Last Five Cells in a Column | Excel Worksheet Functions | |||
Z Chart i.e. top rolling annual bottom rolling monthly middle cum. | Excel Worksheet Functions | |||
average, array and offsets | Excel Worksheet Functions |