3 week rolling average, excluding blanks

[ndc1987]

Hi,

I'm looking for help with a 3 week rolling average.
I have my spreadsheet set up with Data in columns NY through to OT as below

NY NZ OA OB
Week 1 Week 2 Week 3 Week 4
10 20 Blank 15

The data goes right through to Week 22 (column OT). From column OU onwards I am calculating the average.

What I want is for a moving average of the last 3 weeks in which the cell has a value.

For example I would like the average at Week 4, so the average should be (10+20+15)/3.

Then for the average at Week 5, week 5 score + 15 + 20 /3 and so on.

Thanks for your help

 

[Aladin Akyurek]

Hi,

I'm looking for help with a 3 week rolling average.
I have my spreadsheet set up with Data in columns NY through to OT as below

NY NZ OA OB
Week 1 Week 2 Week 3 Week 4
10 20 Blank 15

The data goes right through to Week 22 (column OT). From column OU onwards I am calculating the average.

What I want is for a moving average of the last 3 weeks in which the cell has a value.

For example I would like the average at Week 4, so the average should be (10+20+15)/3.

Then for the average at Week 5, week 5 score + 15 + 20 /3 and so on.

Thanks for your help

OU2, control+shift+enter, not just enter, and copy across:

=AVERAGE(NY2:INDEX(NY2:$OT2,SMALL(IF(ISNUMBER(NY2:$OT2),COLUMN(NY2:$OT2)-COLUMN(NY2)+1),3)))

 

[ndc1987]

Thanks Aladin, I will try that

 

[ndc1987]

Unfortunatly that formula isn't working as when I drag the formula across, it is only calculating the last 3 weeks, not the last 3 weeks in which a score has been recorded. For example if we are at Week 7 and the last 3 scores were in Week 1,2, and 6, its not calculating the correct average.

Any suggestions?

 

[Aladin Akyurek]

Unfortunatly that formula isn't working as when I drag the formula across, it is only calculating the last 3 weeks, not the last 3 weeks in which a score has been recorded. For example if we are at Week 7 and the last 3 scores were in Week 1,2, and 6, its not calculating the correct average.

Any suggestions?

I fear there is some problem involving the notions of "rolling" and "last 3 figures"...

Consider...

[TABLE="width: 672"]
<colgroup><col style="width: 48pt;" span="14" width="64"> <tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]NY[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]NZ[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]OA[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]OB[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]OC[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]OD[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]OU[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]OV[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]OW[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]OX[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


What are the outcomes you want to see under col OU, OV, and so on?

 

[ndc1987]

Thanks for your help Aladin

For OU - the 3 week ave. should be N/A (NY,NZ) as there have only been 2 scores in the 3 week period
For OV - it should be the average of OB,NZ,NY
For OW - it should be the average of OC,OB,NZ
For OX - lets say OD was blank, it still should be the average of OC,OB,NZ

If its too hard to do the formula, don't worry, otherwise any help is appreciated.

 

[Aladin Akyurek]

Thanks for your help Aladin

For OU - the 3 week ave. should be N/A (NY,NZ) as there have only been 2 scores in the 3 week period
For OV - it should be the average of OB,NZ,NY
For OW - it should be the average of OC,OB,NZ
For OX - lets say OD was blank, it still should be the average of OC,OB,NZ

If its too hard to do the formula, don't worry, otherwise any help is appreciated.

What is the version of Excel you are on?

 

[ndc1987]

Excel 2010

 

[Aladin Akyurek]

Excel 2010

That's what we need...

OU2, control+shift+enter, not just enter, and copy across:

=IF(COUNT($NY$2:INDEX($NY$2:$OT$2,3+COLUMNS($OU$2:OU2)-1))>=3,
  AVERAGE(INDEX($NY$2:$OT$2,LARGE(IF(ISNUMBER($NY$2:$OT$2),
   IF(COLUMN($NY$2:$OT$2)-COLUMN($NY$2)+1<=3+COLUMNS($OU$2:OU2)-1,
    COLUMN($NY$2:$OT$2)-COLUMN($NY$2)+1)),3)):INDEX($NY$2:$OT$2,
     3+COLUMNS($OU$2:OU2)-1)),"NA")

 

[ndc1987]

That works perfectly, than you very much for your help!!