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.

"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
---

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

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