How can I average two count totals on reports?

Brooks

Hello!

I have two reports:

Report 1 has the total count of tasks.

Report 2 has the total count of sub-tasks.

How can I automatically calculate the average number of subtasks per task (number of sub-tasks/number of tasks) from these reports?

I know that reports can't pull in ifnormation from other reports, so I'm not quite sure how to accomplish this. Thanks for you assistance!

Genevieve P.

Hi @Brooks

If you're looking for an average number of Sub-Tasks per task, I would actually use a helper column in this source sheet! Your sub-tasks are children of the Parent tasks, which means we can use a really simple formula of:

=COUNT(CHILDREN([Task Name]@row))

You can apply this as a Column Formula and this will give you the number of children for each task. Then you could use the Report to find the Average, or another formula in a Sheet Summary field, like so:

=AVERAGEIF(Category:Category, <>"", [Helper Column]:[Helper Column])

Will that work for you?

Cheers,

Genevieve

Genevieve P.

Hi @Brooks

You're correct, there isn't a way to combine two Reports in this way, or use to Smartsheet items as a source for a Chart in a Dashboard to create these comparisons. Please submit your feature request to our Product team through this form, here.

How many source sheets is each Report referencing? I'm wondering if this may be best calculated using formulas in a Sheet instead of using two Reports. Would you be able to post a screen capture of one of the source sheets (blocking out sensitive data), identifying how you can tell if it's a Task or Sub Task?

Thanks!

Genevieve

Brooks

Hi @Genevieve P.!

Thanks of your help :)

There is only one source sheet. There are two columns used in the task vs. sub-task calculations: Tasks and Categories.

Tasks and sub-tasks are both listed in the Task Name column. Visually, sub-tasks are indicated by an indent in that column. On the report sheet, I differentiated between tasks vs. sub-tasks using the Categories column. Tasks have something listed in the Categories column on the same row. Sub-tasks have a blank Categories field on that row.

For security reasons, I can't post a screen capture of the actual sheet, but here is an example:

Genevieve P.

Hi @Brooks

If you're looking for an average number of Sub-Tasks per task, I would actually use a helper column in this source sheet! Your sub-tasks are children of the Parent tasks, which means we can use a really simple formula of:

=COUNT(CHILDREN([Task Name]@row))

You can apply this as a Column Formula and this will give you the number of children for each task. Then you could use the Report to find the Average, or another formula in a Sheet Summary field, like so:

=AVERAGEIF(Category:Category, <>"", [Helper Column]:[Helper Column])

Will that work for you?

Cheers,

Genevieve

Brooks

Hi @Genevieve P.

This worked perfectly! Thanks so much for your help! :)

Genevieve P.

Wonderful, I'm glad to hear it! 🙂