Calculating 13 weeks moving average

Jessintha Nathan

Hi!

How do I calculate 13 weeks average for a series of data in a column? I need the formula to detect every incremental week add of data.

Thank you!

Jessintha Nathan

Genevieve P.

Hi @Jessintha Nathan

You could use an AVERAGEIF function to do this, looking for the past 13 weeks in one column and averaging the values in another. It looks like you're wanting a cross-sheet formula, where the formula is in a different sheet, is that correct?

Try something like this:

=AVERAGEIF({Column 34 with Dates}, AND(@cell > TODAY(-91), @cell <= TODAY()), {Column 41 to Average})

This will look in Column 34 (your end-date column) to find all the rows where the date is greater than 91 days ago (13 weeks ago), and is equal to or less than Today's date. Then it will average all the numbers in the Data column for those specific rows.

Here are some Help Center articles I used that may help you: @cell Function / Cross Sheet References / AVERAGEIF Function / AND function / TODAY function

Let me know if you need any help or further clarification.

Cheers,

Genevieve

Jessintha Nathan

Hi Genevieve,

Thank you, I am validating the formula.