Averaging lowest values

There may be a short hand way to do this, but you assumin a range A1:a10, how about
=(SMALL($A$1:$A$10,1) + SMALL($A$1:$A$10,2) + SMALL($A$1:$A$10,3) + SMALL($A$1:$A$10,4) + SMALL($A$1:$A$10,5))/5

to avoid an error if there are less than five scores, you could use something like
=IF(COUNT(A1:A10)<5,"only " & COUNT(A1:A10) & " scores exist",(SMALL($A$1:$A$10,1) + SMALL($A$1:$A$10,2) + SMALL($A$1:$A$10,3) + SMALL($A$1:$A$10,4) + SMALL($A$1:$A$10,5))/5)

Using Mark W.'s concept...

=AVERAGE(SMALL(A1:A10,{1;2;3;4;5}))

Where A1:A10 holds the 10 numbers.

Juan Pablo G.

Well, I just learned something new ! (nt)

Close! I think I'm heading in the right direction. It works until I have a blank cell. The range of cells won't always be 10. (A1:A10) Say I have scores for weeks 1 thru 9, no score for week 10 and my 10th score is in the cell for week 11 (A11). I need to be able to get the five lowest out the last 10 entries,(A1:A11) no matter where they fall. Is that possible?

An "average for the LOWEST 5 scores out of the last 10 entered",emphsizing "the last 10", would require:

=AVERAGE(SMALL(OFFSET(A1, MATCH(9.99999999999999E+307,A:A)-10, 0, 10, 1),{1, 2, 3, 4, 5}))

interpreting "Taking into account, there could/will be empty cells when someone doesn't play" as blanks cells should not affect the average computed.

If there can be less than 10 scores in A, then use:

=IF(COUNT(A:A)>=10, AVERAGE(SMALL(OFFSET(A1, MATCH(9.99999999999999E+307,A:A)-10, 0, 10, 1),{1, 2, 3, 4, 5})), AVERAGE(A:A))

Note. The above formulation requires that you don't have any other numbers in A but the ones of interest.

Aladin

======

See my reply above. [NT]

Thanks Aladin.

Juan Pablo G.

Aladin,
This isn't quite doing the trick. It's averaging the lowest 5 scores, but only from the last 10 cells, not 10 weeks like I need. In otherwords, if I'm at week 20, but I have no scores in week 14, 16 & 17, I'm currently getting the low 5 from weeks 11 thru 20. But that's only averaging 5 out of 7 scores. I need it to go further back (3 more weeks) to pick up a total of 10 scores to grab the 5 low scores to average. In this case, I would need to go back to week 8 to have the 10 scores I need. Any thoughts? Thanks!

You want in fact the average of 5 lowest scores of the last 10 non-zero/non-blank scores. That changes things of course.

Array-enter:

=AVERAGE(SMALL(INDIRECT("A" & LARGE(ISNUMBER(OFFSET(A1, 0, 0, MATCH(9.99999999999999E+307,A:A), 1)) * ROW(OFFSET(A1, 0, 0, MATCH(9.99999999999999E+307,A:A), 1)), {1;2;3;4;5;6;7;8;9;10})), {1,2,3,4,5}))

As you see, I use "A" for scores in A. If you need this formula in B, copy the formula and change the "A" bit into "B".

It's of course possible to adapt the formula to a fixed lay-out such that it is copiable across or down and avoid computing some things twice.

Note. In order to array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

=========== ,

Aladin...
I believe that did it! It seems to be working like a charm. I just have to adapt to work across instead of down, but that shouldn't be a problem. I'm glad I ran across this web site. I could have been here for the next year and not figured that one out. Thanks for all your help! -Al C.