# Rolling average by week number

Options

I'd like to automate the calculation of a rolling average:

"Average the value of the past four weeks"

"Average the value of the past 12 weeks"

The 'SUP avg' column is what I want to average, and 'W#' is week number.

I know we can get the current week number with =WEEKNUMBER(TODAY())

though I haven't figured out the rolling average

=AVG(COLLECT([SUP avg]:[SUP avg], [W#]:[W#] = WEEKNUMBER(TODAY()) -3, [W#]:[W#] = WEEKNUMBER(TODAY()) -2))

Tags:

Options

Yes! Thank you so much for this! I knew this could be automated, I just couldn't work out the syntax. Thanks for the formula, and the explanation.

Options

Yes! Thank you so much for this! I knew this could be automated, I just couldn't work out the syntax. Thanks for the formula, and the explanation.

• Employee
Options

Great! So glad this worked for you 🙂

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!