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?

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

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?

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?

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?

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?

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?