| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
pick top 5 out of 8 numbers
have a row of 8 numbers and want to average the top 5 numbers
|
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
pick top 5 out of 8 numbers
Try this:
=AVERAGE(LARGE(A1:H1,{1,2,3,4,5})) If there is less than 5 numbers in the range the formula will return an error. -- Biff Microsoft Excel MVP "poncho" <u49547@uwe wrote in message news:919390a5827bd@uwe... have a row of 8 numbers and want to average the top 5 numbers |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
pick top 5 out of 8 numbers
poncho wrote:
have a row of 8 numbers and want to average the top 5 numbers Thanks Bob Phillips, found it in an earlier thread... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
pick top 5 out of 8 numbers
Thanks T. Valko, needed to know about the error...
T. Valko wrote: Try this: =AVERAGE(LARGE(A1:H1,{1,2,3,4,5})) If there is less than 5 numbers in the range the formula will return an error. have a row of 8 numbers and want to average the top 5 numbers |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
pick top 5 out of 8 numbers
Hi,
You could also consider =SUMPRODUCT(LARGE(A1:H1,ROW(1:5))/5) or to handle errors because of less than 5 numbers: =AVERAGE(LARGE(A1:H1,ROW(INDIRECT("1:"&MIN(5,COUNT (A1:H1)))))) this will calculate the average of the 5 largest or the 4 largest if there are only 4 items and so on. This formula requires array entry (press Shif+Ctrl+Enter to enter it, not Enter.) -- If this helps, please click the Yes button Cheers, Shane Devenshire "poncho" wrote: poncho wrote: have a row of 8 numbers and want to average the top 5 numbers Thanks Bob Phillips, found it in an earlier thread... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| need to set up a formula to pick random numbers | Excel Worksheet Functions | |||
| How to pick up 1 number with a lot of same numbers in row/ column? | New Users to Excel | |||
| Pick the low number in a column of Numbers | Excel Discussion (Misc queries) | |||
| Can Excel pick random numbers from 1-300 and not repeat numbers? | Excel Discussion (Misc queries) | |||
| Possible to pick out the lowest eight numbers in a row? | Excel Discussion (Misc queries) | |||
Linear Mode
