Rolling average by week number

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


Best Answers

  • fred sodergren
    Answer ✓

    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.

Answers

  • fred sodergren
    Answer ✓

    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.

  • Genevieve P.
    Genevieve P. Employee Admin

    Great! So glad this worked for you 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!