software

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 count back
starting with W6
until it counts 10 scores then average them. Some cells may be blank.
Can anyone help?GaryC wrote...
gt;What I am trying to do is track a 10 week rolling average, where scores
gt;are entered weekly in row A6 thru W6. The formula needs to count back
gt;starting with W6
gt;until it counts 10 scores then average them. Some cells may be blank.
gt;Can anyone help?

Without using volatile functions, try

=AVERAGE(INDEX(A6:W6,MAX(1,LOOKUP(1E 300,A6:W6,COL UMN(A6:W6))-9))
:INDEX(A6:W6,LOOKUP(1E 300,A6:W6,COLUMN(A6:W6))))

Using the volatile OFFSET function,

=AVERAGE(OFFSET(A6:W6,0,LOOKUP(1E 300,A6:W6,COLUMN (A6:W6))-1,1,-10))

Both assume there are no gaps in your data, e.g., blank cell K6 between
nonblank cells A6:I6 and L6:P6.

If A6:W6 all allways contain values, then just use =AVERAGE(N6:W6).GaryC wrote...
gt;What I am trying to do is track a 10 week rolling average, where scores
gt;are entered weekly in row A6 thru W6. The formula needs to count back
gt;starting with W6
gt;until it counts 10 scores then average them. Some cells may be blank.
gt;Can anyone help?

Without using volatile functions, try

=AVERAGE(INDEX(A6:W6,MAX(1,LOOKUP(1E 300,A6:W6,COL UMN(A6:W6))-9))
:INDEX(A6:W6,LOOKUP(1E 300,A6:W6,COLUMN(A6:W6))))

Using the volatile OFFSET function,

=AVERAGE(OFFSET(A6:W6,0,LOOKUP(1E 300,A6:W6,COLUMN (A6:W6))-1,1,-10))

Both assume there are no gaps in your data, e.g., blank cell K6 between
nonblank cells A6:I6 and L6:P6.

If A6:W6 all allways contain values, then just use =AVERAGE(N6:W6).

I have a feeling my caveat above about blank cells may be your exact
problem. If so, then use the array formula

=AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6),
--(COLUMN(A6:W6)lt;=TRANSPOSE(COLUMN(A6:W6)))),0)):W6)I tried to copy
=AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6),
--(COLUMN(A6:W6)lt;=TRANSPOSE(COLUMN(A6:W6)))),0)):W6)
into my spreadsheet and it treats it as text not a formula.gt;=AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6),
gt;--(COLUMN(A6:W6)lt;=TRANSPOSE(COLUMN(A6:W6)))),0)):W6)

If there are lt;10 values, returns #N/A.

......MATCH(MIN(COUNT(A6:W6),10).......

Still doesn't account for COUNT = 0

So, maybe:

=IF(COUNT(),AVERAGE(INDEX......................... .)):W6),quot;quot;)

Biff

quot;Harlan Grovequot; gt; wrote in message oups.com...
gt; GaryC wrote...
gt;gt;What I am trying to do is track a 10 week rolling average, where scores
gt;gt;are entered weekly in row A6 thru W6. The formula needs to count back
gt;gt;starting with W6
gt;gt;until it counts 10 scores then average them. Some cells may be blank.
gt;gt;Can anyone help?
gt;
gt; Without using volatile functions, try
gt;
gt; =AVERAGE(INDEX(A6:W6,MAX(1,LOOKUP(1E 300,A6:W6,COL UMN(A6:W6))-9))
gt; :INDEX(A6:W6,LOOKUP(1E 300,A6:W6,COLUMN(A6:W6))))
gt;
gt; Using the volatile OFFSET function,
gt;
gt; =AVERAGE(OFFSET(A6:W6,0,LOOKUP(1E 300,A6:W6,COLUMN (A6:W6))-1,1,-10))
gt;
gt; Both assume there are no gaps in your data, e.g., blank cell K6 between
gt; nonblank cells A6:I6 and L6:P6.
gt;
gt; If A6:W6 all allways contain values, then just use =AVERAGE(N6:W6).
gt;
gt; I have a feeling my caveat above about blank cells may be your exact
gt; problem. If so, then use the array formula
gt;
gt; =AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6),
gt; --(COLUMN(A6:W6)lt;=TRANSPOSE(COLUMN(A6:W6)))),0)):W6)
gt;
You're getting ahead of me. I'm still trying to plug the first formula
in my spreadsheet. I haven't done this before. I did a copy paste and
obviously I need to do something different for the formula to work. It
pasted into two cells.

There very well may be blanks in the string of numbers. The first week
there will be only one number in the row of data. The second week, 2
and so on. After 10 weeks of scores, I want to drop off the oldest
score and average the last 10.Try this array formula (commit with Ctrl shift Enter):

=IF(COUNT(A6:W6),AVERAGE(INDEX(A6:W6,LARGE(IF(A6:W 6lt;gt;quot;quot;,COLUMN(A6:W6)),MIN(COUNT(A6:W6),10))):W6),quot;N O DATAquot;)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;GaryCquot; wrote:

gt; What I am trying to do is track a 10 week rolling average, where scores
gt; are entered weekly in row A6 thru W6. The formula needs to count back
gt; starting with W6
gt; until it counts 10 scores then average them. Some cells may be blank.
gt; Can anyone help?
gt;
gt;

I don't know if this helps or not. I can't seem to get this formula
pasted into my spreadsheet as a formula.Also what does (commit with Ctrl shift Enter): mean?Well at least I got the formula pasted in, but it says it has an error.