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?
-
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.
-
Ding Ding Ding Ding....you are awesome...thanks
-
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!