Calculating Average Time for each member of a team

07/21/21
Answered - Pending Review

Hi everyone,

I am new to SmartSheets and Excel formulas in general and would like some help please. I would like to calculate the average time in months that it takes for a team member to complete tasks. For example, I have a date created column for every task and another column named "Team member". I would like to calculate the number of months since the task was created from today and obtain that number for every task for that corresponding team member and calculate the overall average for all of the tasks in months for every team member.

I currently have " =(NETDAYS(({Reference}, {Reference})/ 365) * 12 " to calculate the number of months for a specific cell, but I would like to do it for all the tasks corresponding to a specific team member.


Any help is greatly appreciated.

Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    Hi @Henry Tam Tran-Quevedo

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hi Bassam,

    Thanks for replying. Say something like this. For every team member, I would like to get the average time (in months) for all of the tasks corresponding to that specific member with the "In Progress" status. As an example, get the number of months since "Task1" was created from today and do that for all of Robert's tasks and then get the average time(In months) for Robert so that I can calculate Robert's overall average aging that includes all of his tasks that are in progress.

    Note: In my screen shot all tasks were created today, but in the actual sheet, all tasks have different dates from many months ago.

    Thank you again. I appreciate the help.


Sign In or Register to comment.