Create Running Average

Sherry Fox

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

Paul Newcome

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))

Sherry Fox

@Paul Newcome ,

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))

Paul Newcome

Column names that have spaces, numbers, and/or special characters have to be wrapped in square brackets.

[Column Name]

Sherry Fox

@Paul Newcome ,

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))

Paul Newcome

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."

Sherry Fox

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!