AVG of ‘Days to Accomplish Task’ column, by ‘Assigned To’ column, by 'Completed' Month

Smartsheet Community,

Trying to build a Key Performance Indicator Trend Analysis Metric Sheet to record the Average (within a given month) of ‘Days to Accomplish Assigned Task’ column, by ‘Assigned To’ column, by ‘Date Completed’ column Month (01/15/21).

Columns will not ALL have data represented, so IFERROR will be needed to mitigate this. And the ‘Assigned’ individuals could change throughout the year, so this will not be a static data set.

From this, ideally looking to turn the data into a graph with Horizontal Axis representing the Months (January to December) and Vertical Axis the Qty of Avg Days worked on Task, and the data plots by month would the ‘Assigned To’ individual and their respective AVG Days to Accomplish Task. This would then auto generate by month as the year progresses and record a trend line by individual by month for the KPI of Avg Days to Accomplish Task.

See below examples.

With Smartsheet Community support, I was able to build a KPI trend analysis sheet/graph for my main Category KPI’s, now trying to build the above to capture the individual performance KPI’s.

Average Days ‘In Progress’ by Month formula.

=IFERROR(AVG(COLLECT({Work IP}, {ACH Request Tracker Range 3},

IFERROR(MONTH(@cell), 0) = [Column5]@row)), 0)

Thank you in advance!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!