Formula to Calculate Rolling Average based on the current Week of the year

Joshua Lewis

I have a Smartsheet that will have values inputted each week and I would like to calculate an historical rolling average based on the last four weeks. What formula would I use to calculate this rolling average?

Example data (Assuming current week is 201905)

4 Week Rolling Average 201901 201902 201903 201904 201905

=average of 201902 - 201905 10 5 4 5 3

Genevieve P.

Hi Joshua,

This may depend on the column types that you have and how your sheet is set up. Would you be able to share a screen capture of your sheet (removing any sensitive data)?

If you have a date column type in your sheet that is recording the week that the data you want averaged is input, then you could use the TODAY function (click here for an article on TODAY).

You could then build out a formula that uses AVG, COLLECT, and TODAY to average your data between 30 days ago and today.

An AVG(COLLECT formula works like this:

=AVG(COLLECT({Range to Average}, {Criteria range 1}, criteria 1, {Criteria range 2}, criteria 2, ...) etc

In your case, with the TODAY function, it might look something like this: 

=AVG(COLLECT([Column to Avg]:[Column to Avg], [Week Date Column]:[Week Date Column], >= TODAY(-30), [Week Date Column]:[Week Date Column], <= TODAY())

Would this work for your purposes?

Cheers,

Genevieve

Joshua Lewis

Thank you for the response. I do not believe that I completely follow how to use the recommended functions. To further help, here is an example of my spreadsheet.

Genevieve P.

Hi Joshua,

In order to automatically calculate a rolling average based on dates without needing to manually change it every week, you would need to have a Date Type column in your sheet so that it can understand when the "last four weeks" are.

To do so, instead of having each week be a new column, you would need to set up each week as a new row, with one column being a Date Type column, selecting the start of each week.

Then you could use my formula above to calculate the rolling average, like so:

If you are going to have many projects and weeks in the same sheet, you may want to have the Rows be the Weeks and have one Column for each project.

Then you could use a Sheet Summary field to calculate the AVG formula, for that one specific task's values, like so:

Once you have decided on the structure of your sheet, let me know if you have any questions about the formula and I'm happy to help again.

Cheers,

Genevieve