Calculating 13 weeks moving average
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!
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Genevieve,
Thank you, I am validating the formula.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!