=AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6),
--(COLUMN(A6:W6)<=TRANSPOSE(COLUMN(A6:W6)))),0)):W6)

If there are <10 values, returns #N/A.

......MATCH(MIN(COUNT(A6:W6),10).......

Still doesn't account for COUNT = 0

So, maybe:

=IF(COUNT(),AVERAGE(INDEX......................... .)):W6),"")

Biff

"Harlan Grove" wrote in message
oups.com...
GaryC wrote...
What I am trying to do is track a 10 week rolling average, where scores
are entered weekly in row A6 thru W6. The formula needs to count back
starting with W6
until it counts 10 scores then average them. Some cells may be blank.
Can anyone help?

Without using volatile functions, try

=AVERAGE(INDEX(A6:W6,MAX(1,LOOKUP(1E+300,A6:W6,COL UMN(A6:W6))-9))
:INDEX(A6:W6,LOOKUP(1E+300,A6:W6,COLUMN(A6:W6))))

Using the volatile OFFSET function,

=AVERAGE(OFFSET(A6:W6,0,LOOKUP(1E+300,A6:W6,COLUMN (A6:W6))-1,1,-10))

Both assume there are no gaps in your data, e.g., blank cell K6 between
nonblank cells A6:I6 and L6:P6.

If A6:W6 all allways contain values, then just use =AVERAGE(N6:W6).

I have a feeling my caveat above about blank cells may be your exact
problem. If so, then use the array formula

=AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6),
--(COLUMN(A6:W6)<=TRANSPOSE(COLUMN(A6:W6)))),0)):W6)