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([5-Apr]1:[24-May]1, >=(WEEKNUMBER(TODAY()) - 3), [5-Apr]3:[24-May]3)
Breaking that down so you can adjust it as needed:
Part 1
=AVERAGEIF([5-Apr]1:[24-May]1, >=(WEEKNUMBER(TODAY()) - 3), [5-Apr]3:[24-May]3)
The first part (in bold above) says looks at the values in row 1 for columns between 5-Apr and 24-May (you can extend this to whatever is the first and last column in your sheet, maybe [5-Jan]1:[27-Dec]1).
Part 2
=AVERAGEIF([5-Apr]1:[24-May]1, >=(WEEKNUMBER(TODAY()) - 3), [5-Apr]3:[24-May]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([5-Apr]1:[24-May]1, >=(WEEKNUMBER(TODAY()) - 3), [5-Apr]3:[24-May]3)
The final part (in bold above) says average the values in row 3 for the columns where this is true. So if RFO-Sent is row 3 this will average RFO-Sent. If you want to average different row, change the 3 to the row number you want to average like this:
=AVERAGEIF([5-Apr]1:[24-May]1, >=(WEEKNUMBER(TODAY()) - 3), [5-Apr]10:[24-May]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([5-Apr]1:[24-May]1, >=(WEEKNUMBER(TODAY()) - 3), [5-Apr]3:[24-May]3)
Breaking that down so you can adjust it as needed:
Part 1
=AVERAGEIF([5-Apr]1:[24-May]1, >=(WEEKNUMBER(TODAY()) - 3), [5-Apr]3:[24-May]3)
The first part (in bold above) says looks at the values in row 1 for columns between 5-Apr and 24-May (you can extend this to whatever is the first and last column in your sheet, maybe [5-Jan]1:[27-Dec]1).
Part 2
=AVERAGEIF([5-Apr]1:[24-May]1, >=(WEEKNUMBER(TODAY()) - 3), [5-Apr]3:[24-May]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([5-Apr]1:[24-May]1, >=(WEEKNUMBER(TODAY()) - 3), [5-Apr]3:[24-May]3)
The final part (in bold above) says average the values in row 3 for the columns where this is true. So if RFO-Sent is row 3 this will average RFO-Sent. If you want to average different row, change the 3 to the row number you want to average like this:
=AVERAGEIF([5-Apr]1:[24-May]1, >=(WEEKNUMBER(TODAY()) - 3), [5-Apr]10:[24-May]10)
Hope this helps.
-
Brilliant! Thank you so much for breaking it down, as well. Extremely helpful!!
-
Happy to help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!