Rolling average by week number

fred sodergren

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

Genevieve P.

Hi Fred,

Try this:

=AVERAGEIF([W#]:[W#], AND(@cell >= WEEKNUMBER(TODAY()) - 3, @cell <= WEEKNUMBER(TODAY())), [SUP Avg]:[SUP Avg])

I used AVERAGEIF because really there's only one criteria... that the date is between 3 weeks ago and today (or 4 weeks, including this week).

First I listed the Week Number range, then with the AND function I stated that within the cell of the W# column, the number needed to be greater than or equal to Today's Weeknumber, minus three... but then we had to say when to STOP the range, which is when it's less than or equal to Today's Weeknumber. At the end I've listed the column to Average.

This can be adapted for 12 weeks by changing the -3 to -11.

Let me know if it works for you!

Cheers,

Genevieve

fred sodergren

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.

Hi Fred,

Try this:

=AVERAGEIF([W#]:[W#], AND(@cell >= WEEKNUMBER(TODAY()) - 3, @cell <= WEEKNUMBER(TODAY())), [SUP Avg]:[SUP Avg])

I used AVERAGEIF because really there's only one criteria... that the date is between 3 weeks ago and today (or 4 weeks, including this week).

First I listed the Week Number range, then with the AND function I stated that within the cell of the W# column, the number needed to be greater than or equal to Today's Weeknumber, minus three... but then we had to say when to STOP the range, which is when it's less than or equal to Today's Weeknumber. At the end I've listed the column to Average.

This can be adapted for 12 weeks by changing the -3 to -11.

Let me know if it works for you!

Cheers,

Genevieve

fred sodergren

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.

Great! So glad this worked for you 🙂