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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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
20,517
Office Version
  1. 365
Platform
  1. Windows
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,175,938
Messages
5,900,424
Members
434,832
Latest member
bornsteij78

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