Problems with offset/average formula

[GaryC]

I am having trouble with the following formula:
=AVERAGE(OFFSET(A6,,COUNT(A6:W6)-10,,10)) . What I am trying to do is
track one's 10 week rolling average golf score, where the scores are
entered weekly in cells A6 thru W6. This works if I have at least 10
scores, but does not work if I have less than 10 scores. What do I
need to do differently? Thanks.

 

[bpeltzer]

I think you could replace each 10 with min(10,count(a6:w6)).

 

[Domenic]

Try...

=AVERAGE(OFFSET(A6,MAX(0,COUNT(A6:W6)-10),0,10))

Hope this helps!

 

[GaryC]

Thank you for your suggestion. I tried this and got some eratic
output. Any further suggestions?

 

[Domenic]

Make that...

=AVERAGE(OFFSET(A6,,MAX(0,COUNT(A6:W6)-10),,10))

Hope this helps!

 

[GaryC]

This works. Thank you soooo much.

 

[GaryC]

Oops, I guess it doesn't work. Getting some really strange results.
Let's start over. 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 average the last 10 scores, no matter
where they are entered between A6 and W6.

 

[GaryC]

One clarification: The formula needs to count back starting with W6
until it counts 10 scores then average them. Some cells may be blank.

 

[Domenic]

To average the last 10 non-blank cells, try...

=AVERAGE(INDEX(A6:W6,LARGE(IF(A6:W6<>"",COLUMN(A6:W6)-COLUMN(A6)+1),10)):
INDEX(A6:W6,MATCH(9.99999999999999E+30,A6:W6)))

....confirmed with CONTROL+SHIFT+ENTER. If there are less than 10 scores
and you want those averaged, try...

=AVERAGE(INDEX(A6:W6,LARGE(IF(A6:W6<>"",COLUMN(A6:W6)-COLUMN(A6)+1),MIN(C
OUNT(A6:W6),10))):INDEX(A6:W6,MATCH(9.99999999999999E+30,A6:W6)))

....also confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!