How can I average two count totals on reports?
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!
Best Answer
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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:
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
This worked perfectly! Thanks so much for your help! :)
-
Wonderful, I'm glad to hear it! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!