Calculating Rolling Average Using AVG(COLLECT())?

edited 12/09/19 in Formulas and Functions


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!



Micro KPI Calculator.png



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!