Average tasks by Domain & Status

Options

I need to average the number of tasks by the domain and status so I can get an accurate depiction of the domain progressions.


Answers

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    edited 08/13/22
    Options

    Hi, Kristin. I don't believe you can accomplish this in a report, because the values you want to average are not numeric. Instead, I think you need to create a separate worksheet where you perform these calculations using cross-sheet references. You could then build a report from that calculation sheet.

    For example, I re-created your data in a worksheet (instead of a report), like so:

    I then created a new worksheet on which I performed some calculations, referencing the sheet above:

    (The "Percent ..." columns just divide each domain's total by the total in the status to get a percentage. So, for example, 25% of all domains In Progress are Domain I, 75% are Domain II, etc.)

    From there, I created this report, which groups the data by Domains and summarizes the values by AVERAGE for each of Not Started, In Progress and Complete:

    I don't know how helpful the report format is here. My preference is to use the percentage columns in the calculation sheet above to plot that data into a graph on a dashboard, which feels more actionable for an end user than the averages in the report. But, you may have a specific reason for needing the average, so gave you both. Hope this helps!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!