Can you collect a rolling 4 of values in a row, rather than column?
I'm trying to get a rolling 4 (and rolling 13) average for these conversion metrics that I track weekly. Can I capture that data across a row, rather than within a column?
Here's a screenshot of a basic average formula for the past 4 weeks. But what I'd like is for the average to automatically calculate the last 4 weeks without me having to manually update the formula every week.
Thanks!
Best Answer

Hi @EagonA
You could do something with weeknumber. I'd use a formula that averages the values in the row if the week number is greater than the current week number minus 3. Like this:
=AVERAGEIF([5Apr]1:[24May]1, >=(WEEKNUMBER(TODAY())  3), [5Apr]3:[24May]3)
Breaking that down so you can adjust it as needed:
Part 1
=AVERAGEIF([5Apr]1:[24May]1, >=(WEEKNUMBER(TODAY())  3), [5Apr]3:[24May]3)
The first part (in bold above) says looks at the values in row 1 for columns between 5Apr and 24May (you can extend this to whatever is the first and last column in your sheet, maybe [5Jan]1:[27Dec]1).
Part 2
=AVERAGEIF([5Apr]1:[24May]1, >=(WEEKNUMBER(TODAY())  3), [5Apr]3:[24May]3)
The next part (in bold above) says check if the value in the cells you are looking at is greater than or equal to the today's weeknumber minus 3. It looks like your week numbers exactly match Smartsheet week numbers with today being week 22, so this is easy. If they do not, some more math could be needed but it looks like it should be OK.
Part 3
=AVERAGEIF([5Apr]1:[24May]1, >=(WEEKNUMBER(TODAY())  3), [5Apr]3:[24May]3)
The final part (in bold above) says average the values in row 3 for the columns where this is true. So if RFOSent is row 3 this will average RFOSent. If you want to average different row, change the 3 to the row number you want to average like this:
=AVERAGEIF([5Apr]1:[24May]1, >=(WEEKNUMBER(TODAY())  3), [5Apr]10:[24May]10)
Hope this helps.
Answers

Hi @EagonA
You could do something with weeknumber. I'd use a formula that averages the values in the row if the week number is greater than the current week number minus 3. Like this:
=AVERAGEIF([5Apr]1:[24May]1, >=(WEEKNUMBER(TODAY())  3), [5Apr]3:[24May]3)
Breaking that down so you can adjust it as needed:
Part 1
=AVERAGEIF([5Apr]1:[24May]1, >=(WEEKNUMBER(TODAY())  3), [5Apr]3:[24May]3)
The first part (in bold above) says looks at the values in row 1 for columns between 5Apr and 24May (you can extend this to whatever is the first and last column in your sheet, maybe [5Jan]1:[27Dec]1).
Part 2
=AVERAGEIF([5Apr]1:[24May]1, >=(WEEKNUMBER(TODAY())  3), [5Apr]3:[24May]3)
The next part (in bold above) says check if the value in the cells you are looking at is greater than or equal to the today's weeknumber minus 3. It looks like your week numbers exactly match Smartsheet week numbers with today being week 22, so this is easy. If they do not, some more math could be needed but it looks like it should be OK.
Part 3
=AVERAGEIF([5Apr]1:[24May]1, >=(WEEKNUMBER(TODAY())  3), [5Apr]3:[24May]3)
The final part (in bold above) says average the values in row 3 for the columns where this is true. So if RFOSent is row 3 this will average RFOSent. If you want to average different row, change the 3 to the row number you want to average like this:
=AVERAGEIF([5Apr]1:[24May]1, >=(WEEKNUMBER(TODAY())  3), [5Apr]10:[24May]10)
Hope this helps.

Brilliant! Thank you so much for breaking it down, as well. Extremely helpful!!

Happy to help!
Help Article Resources
Categories
Check out the Formula Handbook template!