Average % Complete If another Column is Checked
I am trying to get the % Complete for a project by averaging the Project Phases % Complete(which are parents to tasks).
- =AVG([% Complete]:[Project Phase], 1) I used this formula to only calculate the values in the row that has been identified as a Project Phase with a check.
- Answer is 8.39
- =AVG(CHILDREN()) * 100 - These are the values that are calculated for the Project Phase
- 63.33
- 42
- 5
- 0
- 0
- Calculating by hand I get 110/5=22
Please explain how the formula is working (curious) and what is the correct formula to average my project phase %complete to get an estimation of project %complete.
Thanks
Comments
-
So you are trying to average the % Complete for the parent rows?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
yes-
-
Ok. Judging by your post it appears that you already have the Parent rows checked. If that's the case, you could use something along the lines of...
=AVG(COLLECT([% Complete]:[% Complete], [Checkbox Column]:[Checkbox Column], 1))
Smartsheet does not have an AVGIF function that would allow you to AVG a range based on criteria (hierarchy level, etc.), so you have to use the COLLECT function.
Hope this helps.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Ding Ding Ding Ding....you are awesome...thanks
-
Happy to help!
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul and Gwen,
Brand new:
Example: =AVERAGEIF([Value 1]200:[Value 2]280, >100)
Best,
Kara
-
Thanks! I look forward to seeing if I can find some limits on this one. Haha.
I know I asked on another thread, but I am not sure if everyone here is also on there.
Is there an AVERAGEIFS, or will we still need to use AVG(COLLECT(.........)) for multiple sets of criteria?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!