software

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.I think you could replace each 10 with min(10,count(a6:w6)).

quot;GaryCquot; wrote:

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

Try...

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

Hope this helps!

In article . comgt;,
quot;GaryCquot; gt; wrote:

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

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

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

Hope this helps!

In article gt;,
Domenic gt; wrote:

gt; Try...
gt;
gt; =AVERAGE(OFFSET(A6,MAX(0,COUNT(A6:W6)-10),0,10))
gt;
gt; Hope this helps!

This works. Thank you soooo much.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.To average the last 10 non-blank cells, try...

=AVERAGE(INDEX(A6:W6,LARGE(IF(A6:W6lt;gt;quot;quot;,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:W6lt;gt;quot;quot;,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 .comgt;,
quot;GaryCquot; gt; wrote:

gt; Oops, I guess it doesn't work. Getting some really strange results.
gt; Let's start over. What I am trying to do is
gt; track a 10 week rolling average, where scores are entered weekly in row
gt; A6 thru W6. The formula needs to average the last 10 scores, no matter
gt; where they are entered between A6 and W6.

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