Calculate Avg # of Days project open - by person

Lisa Wood

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!

Eric Law

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

Eric Law

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

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