Average highest 16 numbers on a column of 32 numbers

F

Frank

I have a column of numbers, each with a value of 25 or less. I need a formula
to total and average the highest 16 numbers in the column. Any ideas? Thanks.
 
M

Max

Frank said:
I have a column of numbers, each with a value of 25 or less. I need a formula
to total and average the highest 16 numbers in the column. Any ideas? Thanks.
Click to expand...

Something like these 2 expressions, array-entered*
=SUM(LARGE(A1:A100,ROW(1:16)))
=AVERAGE(LARGE(A1:A100,ROW(1:16)))
*Array-enter means press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER

Adapt the range to suit
 
R

Ron Rosenfeld

I have a column of numbers, each with a value of 25 or less. I need a formula
to total and average the highest 16 numbers in the column. Any ideas? Thanks.
Click to expand...

It would be helpful to know what version of Excel you have.

It would also be helpful to know how you want to handle duplicates.

To sum all of the numbers that are equal to or greater the 16th highest value:

=SUMIF(A:A,">="&LARGE(A:A,16))


To average all of the numbers that are equal to or greater the 16th highest
value:

Excel 2007: =AVERAGEIF(A:A,">="&LARGE(A:A,16))
Pre-Excel 2007:

=SUMIF(A:A,">="&LARGE(A:A,16))/COUNT(A:A,">="&LARGE(A:A,16))

To do the same, but only with regard to one entry per "rank":

Sum: =SUMPRODUCT(LARGE(A:A,ROW($1:$16)))
Avg: =SUMPRODUCT(LARGE(A:A,ROW($1:$16)))/16

--ron
 
T

Teethless mama

=SUM(INDEX(LARGE(A1:A100,ROW(1:16)),0))

=AVERAGE(INDEX(LARGE(A1:A100,ROW(1:16)),0))

Just normal ENTER
 
Top