Could I use Smartsheet to measure individual Project Team member performance?

Colten McEwan

Hi team,

I am attempting to collect performance data of Project Teams within my organization. This data will be used internally for improvement purposes. I want to collect the average "Duration" of a task and average "Completion" rate (early, on time, or late) of a task that has been assigned to my Project Teams. Ideally, I would like to be able to display this data on a dashboard for easy visibility.

How do you suggest I go about this?

Thanks so much!

-Colten

EvanShortreed

Hi Colten,

Trying make a task tracker sheet and a summary sheet running the calculations for your dashboard.

• Task Tracker Sheet Columns:
  • Task Name
  • Assigned To
  • Start Date
  • End Date
  • Actual Completion Date
  • Status (Dropdown: Early, On Time, Late)
  • Duration (Formula: [Actual Completion Date]@row - [Start Date]@row)

• Summary Sheet Columns:
  • Average Duration (Formula: =AVG({Task Tracker Duration Column}))
  • Early Count (Formula: =COUNTIF({Task Tracker Status Column}, "Early"))
  • On Time Count (Formula: =COUNTIF({Task Tracker Status Column}, "On Time"))
  • Late Count (Formula: =COUNTIF({Task Tracker Status Column}, "Late"))
  • Total Tasks (Formula: =COUNTA({Task Tracker Status Column}))
  • Average Early Rate (Formula: =[Early Count]@row / [Total Tasks]@row)
  • Average On Time Rate (Formula: =[On Time Count]@row / [Total Tasks]@row)
  • Average Late Rate (Formula: =[Late Count]@row / [Total Tasks]@row)

• Hope this helps!

Colten McEwan

@EvanShortreed, thank you so much for your comment. It is definitely getting me in the right direction. However, I want to get specific with names and the average duration associated with specific project team members. I'm currently working with this formula: =AVG(COLLECT({Task Log Archive Range 1}, {Task Log Archive Range 2}, [Assigned]@row)). But I continually get an UNPARSEABLE error. Not sure what I'm doing wrong… I'm essentially trying to perform a VLOOKUP function, but get the average of all data found in a separate sheet & column that is associated with a specific team Member listed in the same row. Below is a screenshot of what Smartsheet tells me to do. When I apply the formula on the same sheet {Task Log Archive}, the formula works. Your advice and mentorship is greatly appreciated.

Colten McEwan

Colten McEwan

Nevermind. I figured it out. Formula below. And a screenshot for further reference.

=AVG(COLLECT({Task Log Archive Range 1}, {Task Log Archive Range 2}, [Project Team Member Name]@row))

Colten McEwan

For good measure, here's the finalized example sheet. As an FYI, the data shows that I have a 60% Avg Late Rate, but the data is completely made up. So not reflective of my performance at all! 😉

EvanShortreed

Hi Colten,

Glad you were able to troubleshoot the issue! If you ever need further help, let us know!

https://www.entheosstudio.com/