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!

-Robert

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are going to need to have the total number of days stored in a column on the sheet somewhere.


    From there you can create a report and use the grouping and summary features to get your averages.

  • Robert B
    Robert B ✭✭✭

    Thanks @Paul Newcome

    I am able to pull a Report with the salient data points, and I can Summarize and Average, but this average will be for all 'Days Work In Progress' not segregated by Month.


    Hopeful that I can run a formula from the above report on a separate metric sheet that will categorize by 'Assigned To' individual by Month, the AVG for the 'Days Work In Progress'. Any suggestions?

    Thank you!

    -Robert

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You should be able to group by the Assigned To and then again by the month. Or is the column housing the month in your original screenshot not on the source sheet?

  • Robert B
    Robert B ✭✭✭

    @Paul Newcome

    Original Screenshot at top of page is a metric sheet where I am running formulas to collect data to show on a dashboard (KPI) Screenshot.

    The Excel screenshot above is a condensed sample of the Source Sheet data.

    In pulling a 'REPORT' I can group by 'assigned to' and sort by ascending or descending Months, but not group and sort by assignee and month..

    I am thinking somewhere along the lines of leveraging a helper column in the Source Sheet that defines the MONTH of the Completion Date (January, February, Etc...) and then I can add that to the report and possibly filter by that variable.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!