Rolling 4 week averages

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
I need to generate a formula that will give me a rolling 4 week (4 cells) forecast or average.

Data:-

Column A: Sales Data

=AVERAGE(A4:A7) and drag it down? Or is there a more professional way to do a rolling average or a forecast?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Eddie Wildon

Board Regular
Joined
Jul 29, 2005
Messages
83
Can you tell me what your desired outcome is?

a straight average would use something like this :

Code:
=if(iserror(average($a4:$a7)),"",average($a4:$a7))

buy say if you wanted to weight it against the number of eligable weeks (assuming not everyone has all 4 weeks)

Code:
=if(iserror(sum($a4:$a7)/count($a4:$a7)),"",sum($a4:$a7)/count($a4:$a7))

Hope this helps. Just guessing as to what you need though....your message is kinda vague.
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
Thats great.

Data is daily and always in Column A.

I'm looking for a dynamic average where the formula will always calculate the last 4 rows of data. I think I need an index(match) or vice versa to grab the last 4 and calculate an average? I'll search the forums and see if there is any similar postings .......

Thanks.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,696
To average the last 4 cells in Column A, try the following...

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then try the following...

=AVERAGE(INDEX(A2:A65536,MATCH(BigNum,A2:A65536)-3):INDEX(A2:A65536,MATCH(BigNum,A2:A65536)))

Hope this helps!
 

Forum statistics

Threads
1,136,352
Messages
5,675,269
Members
419,558
Latest member
Ryanms

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