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.

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.

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
 

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

Top