An eqation to add the largest 6 (or N) numbers from a list
 

I am trying to get an average of list, but if there are more than 6 numbers
in the list to only use the top 6 values. My first thought was some sort of
sum of top six then a count of numbers from the list (in case there are not 6
numbers in the list).
Any help is appreciated!

An eqation to add the largest 6 (or N) numbers from a list
 

Hi,

Your message header and message body are contradictory is it sum or average?

try this

=AVERAGE(LARGE(A1:A10,ROW(INDIRECT("1:6"))))

change average to sum for sum

Mike

"cellofellow" wrote:

I am trying to get an average of list, but if there are more than 6 numbers
in the list to only use the top 6 values. My first thought was some sort of
sum of top six then a count of numbers from the list (in case there are not 6
numbers in the list).
Any help is appreciated!

An eqation to add the largest 6 (or N) numbers from a list
 

OOPs

Mised that there may be less than 6 numbers in the range

=AVERAGE(LARGE(A1:A10,ROW(INDIRECT("1:"&MIN(COUNT( A1:A10),6)))))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

Mike

"Mike H" wrote:

Hi,

Your message header and message body are contradictory is it sum or average?

try this

=AVERAGE(LARGE(A1:A10,ROW(INDIRECT("1:6"))))

change average to sum for sum

Mike

"cellofellow" wrote:

I am trying to get an average of list, but if there are more than 6 numbers
in the list to only use the top 6 values. My first thought was some sort of
sum of top six then a count of numbers from the list (in case there are not 6
numbers in the list).
Any help is appreciated!

An eqation to add the largest 6 (or N) numbers from a list
 

Try this:

=IF(COUNT(A1:A10)=0,0,IF(COUNT(A1:A10)<=6,AVERAGE( A1:A10),AVERAGE(LARGE(A1:A10,{1,2,3,4,5,6}))))

If there are more than 6 numbers, averages the top 6 numbers.

If there is <=6 numbers, averages what's there.

--
Biff
Microsoft Excel MVP


"cellofellow" wrote in message
...
I am trying to get an average of list, but if there are more than 6 numbers
in the list to only use the top 6 values. My first thought was some sort
of
sum of top six then a count of numbers from the list (in case there are
not 6
numbers in the list).
Any help is appreciated!

An eqation to add the largest 6 (or N) numbers from a list
 

Thanks Mike,
For the equation and for interpreting my inconsistency,; it is really
average I was after!

"Mike H" wrote:

OOPs

Mised that there may be less than 6 numbers in the range

=AVERAGE(LARGE(A1:A10,ROW(INDIRECT("1:"&MIN(COUNT( A1:A10),6)))))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

Mike

"Mike H" wrote:

Hi,

Your message header and message body are contradictory is it sum or average?

try this

=AVERAGE(LARGE(A1:A10,ROW(INDIRECT("1:6"))))

change average to sum for sum

Mike

"cellofellow" wrote:

I am trying to get an average of list, but if there are more than 6 numbers
in the list to only use the top 6 values. My first thought was some sort of
sum of top six then a count of numbers from the list (in case there are not 6
numbers in the list).
Any help is appreciated!