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
Answers
-
You would leverage the date column and pull the durations where the date is less than or equal to the date "@row".
=AVG(COLLECT(Duration:Duration, Date:Date, @cell<= Date@row))
-
I am just not getting it. All of this is on the same sheet, so I keep getting Unparsable. And it won't let me name ranges since I am not accessing another sheet. I know I completely messed this up, but unlike Excel, I am not understanding how to create a formula on the same sheet I am actively working on. I am sorry, I just don't get it.
=AVG(COLLECT(Plan / Develop - Duration:Plan / Develop - Duration, Plan / Develop - End Date:Plan / Develop - End Date, [Plan / Develop - Duration]@row<= [Plan / Develop - End Date]@row))
Sherry Fox
Business Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @Sherry Fox
-
Column names that have spaces, numbers, and/or special characters have to be wrapped in square brackets.
[Column Name]
-
I think I am almost there, just one more question. The original formula you provided was:
=AVG(COLLECT(Duration:Duration, Date:Date, @cell<= Date@row))
What would the bold portion be in my case. Below is MY formula (had no clue about the square brackets, thanks for that info!). And a screenshot of my current result. Is this correct? In MY formula, the last 2 variabl;es for the formula are identical.
=AVG(COLLECT([Plan / Develop - Duration]:[Plan / Develop - Duration], [Plan / Develop - End Date]:[Plan / Develop - End Date], [Plan / Develop - Duration]@row <= [Plan / Develop - End Date]@row))
Sherry Fox
Business Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @Sherry Fox
-
You would use just "@cell". It is basically telling the formula to evaluate the previously established range on a cell by cell basis. Thing of it as "the cell".
=COUNTIFS(Column:Column, @cell = 1)
"Evaluate the range and count how many times the cell is equal to 1."
-
Thanks @Paul Newcome . Ironically, the final result was identical to my previous formula.
=AVG(COLLECT([Plan / Develop - Duration]:[Plan / Develop - Duration], [Plan / Develop - End Date]:[Plan / Develop - End Date], @cell <= [Plan / Develop - End Date]@row)).
Appreciate all your help. Always nice to understand something new!
Sherry Fox
Business Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @Sherry Fox
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!