Average of Last 10

[GaryC]

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?

 

[Harlan Grove]

GaryC wrote...

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?

Without using volatile functions, try

=AVERAGE(INDEX(A6:W6,MAX(1,LOOKUP(1E+300,A6:W6,COLUMN(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 L66.

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

 

[Harlan Grove]

GaryC wrote...

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?

Without using volatile functions, try

=AVERAGE(INDEX(A6:W6,MAX(1,LOOKUP(1E+300,A6:W6,COLUMN(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 L66.

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)<=TRANSPOSE(COLUMN(A6:W6)))),0)):W6)

 

[GaryC]

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

 

[Biff]

=AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6),

--(COLUMN(A6:W6)<=TRANSPOSE(COLUMN(A6:W6)))),0)):W6)

If there are <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),"")

Biff

 

[GaryC]

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.

 

[Ron Coderre]

Try this array formula (commit with Ctrl+shift+Enter):

=IF(COUNT(A6:W6),AVERAGE(INDEX(A6:W6,LARGE(IF(A6:W6<>"",COLUMN(A6:W6)),MIN(COUNT(A6:W6),10))):W6),"NO DATA")

Does that help?

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

XL2002, WinXP-Pro

 

[GaryC]

I don't know if this helps or not. I can't seem to get this formula
pasted into my spreadsheet as a formula.

 

[GaryC]

Also what does (commit with Ctrl+shift+Enter): mean?

 

[GaryC]

Well at least I got the formula pasted in, but it says it has an error.

 

[Harlan Grove]

GaryC wrote...

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

Make sure there are no leading spaces before the initial = character.
Some browser-based newsgroup portals, like Google Groups, add invisible
characters that screw up copying from your browser and pasting into
Excel.

 

[Biff]

Select the cell with the formula.

Double click on that cell then, hold down both the CTRL key and the SHIFT
key then hit ENTER.

If done properly the formula will be enclosed in squiggly braces { }. You
can't just type the braces in, you MUST use the key combo of
CTRL,SHIFT,ENTER. Also, if you edit the formula (which is what you did when
you double clicked the formula cell) it must be re-entered as an array using
the key combo.

Biff

 

[Harlan Grove]

Biff wrote...

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

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

Good point, if averages of fewer than 10 values should be permitted.

Still doesn't account for COUNT = 0

So, maybe:

=IF(COUNT(),AVERAGE(INDEX..........................)):W6),"")

Depends. AVERAGE of a range of blank cells returns #DIV/0!, which is
the correct result. After your change, using MIN, the array formula
would also return #DIV/0! if count were 0. Sometimes error results are
the correct and expected result.

 

[Biff]

Sometimes error results are the correct and expected result.

Agreed

Biff

 

[Ron Coderre]

Sorry about the delay....I've been away from my PC for the last 4 hours.

I'm glad Biff jumped in here with the answer you needed.

Did the formula work for you?

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

XL2002, WinXP-Pro

 

[GaryC]

I really apreciate you guys trying to help me, but all this is so far
over my head I am still having problems getting any formula to work at
all. I've tried copying your formulas into note pad, putting it all on
one line and then copying it into my spreadsheet. Since I don't
understand the formula, I'm not sure if I'm suppose to insert or delete
things from what you have posted. I see "--" in a couple of places.
Am I supposed to replace this with something? Any further help will be
greatly appreciated.

 

[Ron Coderre]

To mitigate some of the apparent confusion, I emailed the OP and received the
actual scenario he is using. The range containing the values is F4:AB4, so
the column number difference between Col_A and Col_F must be accounted for.

I sent him this array formula, which I believe will work for him (I'm
waiting to hear back):
AC4:
=IF(COUNT(F4:AB4),AVERAGE(INDEX(F4:AB4,LARGE(IF(F4:AB4<>"",COLUMN(F4:AB4)-COLUMN(F4)+1),MIN(COUNT(F4:AB4),10))):AB4),"NO DATA")

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

XL2002, WinXP-Pro