Problems with offset/average formula

G

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.
 
G

GaryC

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

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.
 
G

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.
 
D

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top