Calculate Avg # of Days project open - by person

Hi all -

I need to create a formula that displays the average number of days a project is open, filtered by person. If this was a report it would filter all rows by person then calculate the average from there.

How would you approach this? Seems simple but it's not working for me. Thanks!

Tags:

Best Answer

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓

    Hello @Lisa Wood, you would need a few things for this to work, which I will lay out just to assume you don't have any of these.

    Pre-req:

    Assigned to column (Assigned) for the name of person

    Status of project (Status) for Open and Closed or whatever

    Duration of project open (Duration). There's a few ways you can populate this.

    Formula: =AVG(COLLECT(Duration:Duration, Assigned:Assigned, "Assigned person", Status:Status, "Open"))

Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓

    Hello @Lisa Wood, you would need a few things for this to work, which I will lay out just to assume you don't have any of these.

    Pre-req:

    Assigned to column (Assigned) for the name of person

    Status of project (Status) for Open and Closed or whatever

    Duration of project open (Duration). There's a few ways you can populate this.

    Formula: =AVG(COLLECT(Duration:Duration, Assigned:Assigned, "Assigned person", Status:Status, "Open"))

  • Lisa Wood
    Lisa Wood ✭✭✭✭

    Thank you for that! I had to add some cross-sheet references but it seems to be working. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!