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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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,719
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,140,917
Messages
5,703,167
Members
421,279
Latest member
emzy

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