Calculating Rolling Average Using AVG(COLLECT())?
Hi,
I am attempting to create a rolling average to replicate a "trend line" one might see in excel graph. Having trouble visualizing the formula I might use, but landed on AVG(COLLECT()) as a plausible option. Hoping to poll the group. I've attached a photo of the sheet.
Essentially I want an average of all preceding months. so the Rolling average of January would just be the "TOTAL PLANT INDEX" (column header = Number of Wild Yeast Samples) but the average of February would be (January+February/2), March would be (January+February+March/3)... and so on. I continue to trip up every time I try. Can't even get good old fashioned average to work since it averages the children as well and ultimately messes up the number, just want to look at the parents!
Perhaps a better bet would be a metric sheet and cell linking? That feels like a step I don't need to take though.
Any help would be appreciated! Thanks!
Comments
-
Try something like this...
=AVG(COLLECT([Column to Average]:[Column to Average], Month:Month, @cell <= MONTH(TODAY())))
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
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!