Is it possible to calculate averages by grouping weekly columns

[balrajd]

I have a spreadsheet where a new set of data is entered each week of the year. Future columns are left blank until that weeks data is entered.

There is an average column already but I would like to add a new column for a running 8 week average which recalculates as each weeks new data is entered. How do I find out the latest 8 weeks in a formula?

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[Estevaoba]

As Richard said, it is always easier when you post a sample workbook to work on.
However, I recently did something similar and I guess you are looking for something like this.
Assuming a range like A5:T5, try this for your mobile average of latest eight values:

Please Login or Register  to view this content.

It's an array formula, so paste it and confirm with Ctrl+Shift+enter instead of just Enter.

Good luck!

[balrajd]

Thanks for that guys, I'm new to this.

I've uploaded a workbook with sales numbers for each week out of 52. I've given an example of what the formula should achieve based on the current dates in the year. I'm hoping to fund one formula that will calculate this based on the last 8 columns that are not blank which will give me the rolling 8 week average.

[Estevaoba]

Try this in C7 and copy down:
=AVERAGE(OFFSET($D$3,,ROW($Z1)-1,1,8))

[balrajd]

Seems promising but I don't understand the use of row($Z1)

[bebo021999]

If C3 need to do average of last 8 columns those have updated value from the most right

In C3 then copy down:

Please Login or Register  to view this content.

[balrajd]

That works, thanks very much!