Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average out of bottom 10 values
I have 20 values in a row or column and what to average the lowest 10 values
of these 20. Is there a formula I can use? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average out of bottom 10 values
Try: =AVERAGE(SMALL(A1:A20,{1,2,3,4,5,6,7,8,9,10})) adjust range to suit -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122623 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average out of bottom 10 values
Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Try the below with numbers in A1:A20 =AVERAGE(SMALL(A1:A20,ROW(A1:A10))) If this post helps click Yes --------------- Jacob Skaria "picks72" wrote: I have 20 values in a row or column and what to average the lowest 10 values of these 20. Is there a formula I can use? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average out of bottom 10 values
Hi,
If by lowest you mean last 20 values, then use =AVERAGE(OFFSET(G23,-9,,10,1)) where G23 is the 20th value. fi by lowest value you mean the smallest 10 numbers, then use =AVERAGE(SMALL(G4:G23,{1,2,3,4,5,6,7,8,9,10})) Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "picks72" wrote in message ... I have 20 values in a row or column and what to average the lowest 10 values of these 20. Is there a formula I can use? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average out of bottom 10 values
Thankyou, works well appreciate the help
"Jacob Skaria" wrote: Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Try the below with numbers in A1:A20 =AVERAGE(SMALL(A1:A20,ROW(A1:A10))) If this post helps click Yes --------------- Jacob Skaria "picks72" wrote: I have 20 values in a row or column and what to average the lowest 10 values of these 20. Is there a formula I can use? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average out of bottom 10 values
Hi,
In 2007 you can do this without an array formula: =AVERAGEIF(A1:A20,"<"&SMALL(A1:A20,11)) In 2003 use the array: =AVERAGE(SMALL(A1:A20,ROW(1:10))) array - means you enter this by pressing Shift+Ctrl+Enter -- If this helps, please click the Yes button. Cheers, Shane Devenshire "picks72" wrote: I have 20 values in a row or column and what to average the lowest 10 values of these 20. Is there a formula I can use? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average out of bottom 10 values
"Shane Devenshire" wrote:
In 2007 you can do this without an array formula: =AVERAGEIF(A1:A20,"<"&SMALL(A1:A20,11)) I don't believe that works when the 11th smallest is equal to the 10th smallest. Test with the following array: 10,20,...,90,100,100,120,...,200 The average of the first 10 should always be 55. PS: In both Excel 2003 and 2007, the following non-array formula is equivalent to that AVERAGEIF for 10 numbers: =sumproduct(--(A1:A20<small(A1:A20,11)),A1:A20) / 10 ----- original message ----- "Shane Devenshire" wrote in message ... Hi, In 2007 you can do this without an array formula: =AVERAGEIF(A1:A20,"<"&SMALL(A1:A20,11)) In 2003 use the array: =AVERAGE(SMALL(A1:A20,ROW(1:10))) array - means you enter this by pressing Shift+Ctrl+Enter -- If this helps, please click the Yes button. Cheers, Shane Devenshire "picks72" wrote: I have 20 values in a row or column and what to average the lowest 10 values of these 20. Is there a formula I can use? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=average - eliminate the top and bottom number | Excel Discussion (Misc queries) | |||
possible have sum and average at bottom? | Excel Discussion (Misc queries) | |||
in the average sum etc at bottom of sheet add a custom tab | Excel Worksheet Functions | |||
calculating average on bottom 50% of data | Excel Discussion (Misc queries) | |||
Average top 10 or bottom 10 | Excel Worksheet Functions |