# Rolling 4 week averages

#### Artorius

##### Board Regular
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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.

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.

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!

Worked perfectly Domenic. Many thanks.

Replies
4
Views
249
Replies
4
Views
239
Replies
4
Views
346
Replies
2
Views
181
Replies
0
Views
547

### Forum statistics

1,221,004
Messages
6,157,338
Members
451,415
Latest member
LettersFromSeneca

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

### Which adblocker are you using?

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

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