3 week rolling average, excluding blanks

ndc1987

New Member
Joined
Jul 19, 2012
Messages
6
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Jul 19, 2012
Messages
6
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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...

NYNZOAOBOCOD OUOVOWOX
1020 126

<colgroup><col style="width: 48pt;" span="14" width="64"> <tbody>
</tbody>


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

ndc1987

New Member
Joined
Jul 19, 2012
Messages
6
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Excel 2010

That's what we need...

OU2, control+shift+enter, not just enter, and copy across:
Rich (BB code):
=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")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,820
Messages
5,598,301
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top