# Average % Complete If another Column is Checked

Options
✭✭✭✭
edited 12/09/19

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.
• =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

Tags:

• ✭✭✭✭✭✭
Options

So you are trying to average the % Complete for the parent rows?

• ✭✭✭✭
edited 05/16/19
Options

yes-

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

Ding Ding Ding Ding....you are awesome...thanks

• ✭✭✭✭✭✭
Options
• Employee
Options

Hi Paul and Gwen,

Brand new:

Example: =AVERAGEIF([Value 1]200:[Value 2]280, >100)

Best,

Kara

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!