Rolling 4 week averages

[Artorius]

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?

 

[Eddie Wildon]

Can you tell me what your desired outcome is?

a straight average would use something like this :

=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)

=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]

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]

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!

 

[Artorius]

Worked perfectly Domenic. Many thanks.