Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Problems with offset/average formula

0 views
Skip to first unread message

GaryC

unread,
Mar 14, 2006, 11:34:31 AM3/14/06
to
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.

Domenic

unread,
Mar 14, 2006, 1:53:28 PM3/14/06
to
Try...

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

Hope this helps!

In article <1142354071....@u72g2000cwu.googlegroups.com>,

GaryC

unread,
Mar 14, 2006, 2:52:11 PM3/14/06
to
Thank you for your suggestion. I tried this and got some eratic
output. Any further suggestions?

Domenic

unread,
Mar 14, 2006, 3:04:59 PM3/14/06
to
Make that...

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

Hope this helps!

In article <domenic22-D31A5...@msnews.microsoft.com>,

GaryC

unread,
Mar 14, 2006, 3:15:41 PM3/14/06
to
This works. Thank you soooo much.

GaryC

unread,
Mar 15, 2006, 9:35:57 AM3/15/06
to
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

unread,
Mar 15, 2006, 12:33:24 PM3/15/06
to
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

unread,
Mar 15, 2006, 2:14:46 PM3/15/06
to
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!

In article <1142433357.6...@z34g2000cwc.googlegroups.com>,
"GaryC" <call...@us.ibm.com> wrote:

> One clarification: The formula needs to count back starting with W6

0 new messages