Average 10 lowest values in range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
Click to expand...
contains blank values. There could be 3 values or 20
values in the range at any given time.
 
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
Click to expand...
contains blank values. There could be 3 values or 20
values in the range at any given time.
 
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
 
Hi
have you entered the formula with CTRL+SHIFT+ENTER as
array formula?
This formula should work
 
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.
 
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.
 
Back
Top