Average 10 lowest values in range

[JACKPOT]

I need to average the 10 lowest values in a range that contains blank values. There could be 3 values or 20 values in the range at any given time.

 

[Jason Morin]

Try:

=AVERAGE(SMALL(A1:A20,ROW(INDIRECT("1:"&MIN(10,COUNT
(A1:A20))))))

Array-entered, meaning press ctrl/shift/enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
I need to average the 10 lowest values in a range that

contains blank values. There could be 3 values or 20
values in the range at any given time.

 

[Frank Kabel]

Hi
and another approach: Array entered:
=AVERAGE(IF(A1:A20<=SMALL(A1:A20,20),A1:A20))

-----Original Message-----
I need to average the 10 lowest values in a range that

contains blank values. There could be 3 values or 20
values in the range at any given time.

 

[JACKPOT]

Thank You for the suggestion. I tried the formula you have
sent below and I just get the smallest value returned, not
the average of the lowest numbers.

Let me explain more in detail of what I am trying to do. I
am entering a score each time a person plays. It is
possible that the person doesn't play every week. I need
to return the average of the lowest 10 scores. I am
running into a problem at week 11 and beyond. At week 11,
my range of scores are from C14 to M14. I have a total of
3 scores in for the last 11 weeks. The first score is 45
in week1 (C14). The second score is 55 in week 4(F14). The
third score is 47 in week 11(M14). There is a possible of
20 weeks that scores can be recoreded (Range C14:V14)

How can I continue to take the average of the 10 lowest
scores? I have adjusted your suggested formula to meet my
range, etc. AVERAGE(SMALL(C14:M14,COLUMN(INDIRECT("1:"&MIN
(10,COUNT(C14:M14)))))). I still continue to get the
lowest score.

I appreciate any info or formula suggestions you may have.
Thank You,

JTJACKPOT

 

[Frank Kabel]

Hi
have you entered the formula with CTRL+SHIFT+ENTER as
array formula?
This formula should work

 

[Guest]

No I haven't done that.
When do I press CTL+SHIFT+Enter? Do I Paste the formula
then press CTL+SHIFT+ENTER? I have never done this before.
Thanks.

 

[Frank Kabel]

Hi
just paste the formula in the formula bar and then press
CTRL+SHIFT+ENTER instead of a single ENTER. <After this the formula
should show in the formula with curly brackets. e.g. {=formula}

Also see: http://www.cpearson.com/excel/array.htm

 

[Daniel.M]

Hi Frank,

I think you meant
=AVERAGE(IF(A1:A20<=SMALL(A1:A20,10),A1:A20))
^
You have to be careful with this one when there are more than 1 cell sharing the
10th smallest, you'll get wrong results.

Regards,

Daniel M.

 

[Frank Kabel]

Hi Daniel
thanks for the typo correction

 

[JACKPOT]

Thanks for all your help. The suggested formula worked
great!