Create Running Average

I need to create a new column with a running average based on the duration column, sounds simple, right? NOT! The issue is the data. In the screenshot below, shows the first section. In this instance the 2nd record shows the end date that happened first. So, as a result, that running average would be ONLY that one record. Then the 1st record has the end date that happened second, so that running average would consist of record #1 and #2. Now the 3rd record happed last for the end date, that one is easy, the running average would be an average of all 3 durations. So how do I factor in the fact that the end dates will always be out of order, yet they determine how this running average is calculated. And the next phase of dates, record 3 has the 1st end date so there is no consistency. I need a formula that can take this criteria into account. Thank you in advance for all your help. This one makes my brain hurt! LOL

Sherry Fox

Business Process Analyst 3 | C5ISR Group

HII | Mission Technologies

EAP | Mobilizer | Automagician | Superstar | Community Champion

Original Smartsheet Profile: @Sherry Fox

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!