Average highest 16 numbers on a column of 32 numbers
 

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.
--
Live long and prosper.

Average highest 16 numbers on a column of 32 numbers
 

"Frank" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Average highest 16 numbers on a column of 32 numbers
 

On Thu, 1 May 2008 16:41:00 -0700, Frank wrote:

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

Average highest 16 numbers on a column of 32 numbers
 

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

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

Just normal ENTER


"Frank" wrote:

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.
--
Live long and prosper.