Can you collect a rolling 4 of values in a row, rather than column?

EagonA

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!

KPH

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.

KPH

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.

EagonA

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

KPH

Happy to help!