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

  • Paul Newcome
    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
    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))

    Sherry Fox

    Business Process Analyst 3 | C5ISR Group

    HII | Mission Technologies

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    Original Smartsheet Profile: @Sherry Fox

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    [Column Name]

  • Sherry Fox
    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))


    Sherry Fox

    Business Process Analyst 3 | C5ISR Group

    HII | Mission Technologies

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    Original Smartsheet Profile: @Sherry Fox

  • Paul Newcome
    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
    Sherry Fox ✭✭✭✭✭✭
    edited 05/18/23

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!