average out of bottom 10 values

  • Thread starter Thread starter picks72
  • Start date Start date
P

picks72

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?
 
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
 
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
 
Thankyou, works well appreciate the help

Jacob Skaria said:
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
Click to expand...
 
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
 
Shane Devenshire said:
In 2007 you can do this without an array formula:
=AVERAGEIF(A1:A20,"<"&SMALL(A1:A20,11))
Click to expand...

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 -----
 
Back
Top