Problems with offset/average formula
 

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.

Problems with offset/average formula
 

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

"GaryC" wrote:

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.

Problems with offset/average formula
 

Try...

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

Hope this helps!

In article . com,
"GaryC" wrote:

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.

Problems with offset/average formula
 

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

Problems with offset/average formula
 

Make that...

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

Hope this helps!

In article ,
Domenic wrote:

Try...

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

Hope this helps!

Problems with offset/average formula
 

This works. Thank you soooo much.

Problems with offset/average formula
 

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.

Problems with offset/average formula
 

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

Problems with offset/average formula
 

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.999999999999 99E+30,A6:W6)))

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

Hope this helps!

In article .com,
"GaryC" wrote:

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.

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