| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pulling the largest numbers | Excel Worksheet Functions | |||
| Highlighting the 5 Largest Numbers in a list | Excel Discussion (Misc queries) | |||
| 3 largest numbers | Excel Discussion (Misc queries) | |||
| return largest number of a list of numbers in the same cell | Excel Worksheet Functions | |||
| average of kth largest numbers in an array of n numbers | Excel Worksheet Functions | |||
Linear Mode
