Ok, try this array formula**.

This is what it'll do:

If there are 0 scores entered it will leave the cell blank.

If there are <10 scores it will average whatever scores are available.

If there are =10 and <=20 scores it will average the lowest 10 scores.

If there are 20 scores it will average the lowest 10 scores out of the last
20 scores.

Important: if a golfer doesn't play that day/week (whatever) *leave the cell
empty*. Don't enter a 0 or a dash "-". Don't enter anything!

The formula is kind of long so to save a few keystrokes create this defined
name:

Goto the menu InsertNameDefine
Name: Array
Refers to: ={1,2,3,4,5,6,7,8,9,10}
OK

After you've created that name go back and make sure Excel hasn't changed
it. Excel has a habit of adding quotes to named strings like that. If it
added any quotes remove them then cuss loudly at Excel!

OK, assuming the range of scores will be in B2:AB2...

Array entered** :

=IF(COUNT(B2:AB2)=0,"",IF(COUNT(B2:AB2)<10,AVERAGE (B2:AB2),IF(COUNT(B2:AB2)<=20,AVERAGE(SMALL(B2:AB2 ,Array)),AVERAGE(SMALL(AB2:INDEX(B2:AB2,LARGE((B2: AB2<"")*(COLUMN(B2:AB2)),20)-COLUMN(B2)+1),Array)))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
I am using Exvel 2003.

The 1st column will be the Golfer's name. The second column will contain
all
the scores for a particular week. The next column will contain the next
week
scores and so on.

I am trying to get 20 columns that are greater than 0 and take the 10
lowest
scores. It may take 22 weeks of scores if a golfer was absent 2 of the
weeks.

I presently have a formula that averages the last 5 scores. We wouuld like
to change this to the low 10 of the last 20 golfed.

"T. Valko" wrote:

If a golfer doesn't play that day *leave the cell empty*. Don't enter a 0
or
a dash "-", don't enter anything!

What version of Excel are you using? Trying to put all these conditions
into
a single formula is pushing the nested function limit to the max! What is
the first column where the scores are entered?

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
I would like to use the 10 lowest scores from the last 20 rounds played.
I
don't want to consider the weeks not played.

So I may have a row with 26 cells and with 2 cells that are blank (or
0)
because the golfer did not show up to golf those weeks. I would like to
go
from right to left in the row and consider the first 20 cells that are
not
0
or blank and then take the lowest 10 of these 20 scores and average
those
10
cells. (The reason for going right to left is that I will add a new
score
to
the end of the row when the golfer play another round the following
week
(after the 45 in the example below)

For example - scores over the past 25 weeks:


41, 40, 42, 38, 46, 44, 41, -, 39, 40, 41, 40, 43, 42, 41, 40, 42, -,
40,
41, 42, 38, 40, 42, 45


I would like to begin considering the score of 45 in week 25 and
consider
the previous 20 nonzero scores going the left (in this example it would
take
be to the 38 in week 4). From those 20 nonzero scores, take the 10
lowest
scores and develop an average. Assuming the golfer showed-up in week 26
I
would repeat the process beginning with the score made in week 26 and
going
to the 46 in week 5.


Other considerations:

For players with between 10 & 20 scores, take the lowest 10 nonzero
scores
and average them. For players with less than 10 scores, take an average
of
all nonzero scores.

Please help with the formula to accomplish this