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

Find more posts tagged with

Formulas

Comments

Paul Newcome

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

gwen.pino78261

yes-

Paul Newcome

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.

gwen.pino78261

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

Paul Newcome

Happy to help!

Kara Lumley

Hi Paul and Gwen,

Brand new:

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

Best,

Kara

Paul Newcome

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Looking for assistance with a formula
Hi all, I am new to formulas and functions in Smartsheet and have a large sheet that I would like to summarize using an "average" formula/function. I need to include at least 2 separate criteria to filter correctly and I am having trouble combining the avg and collect functions as suggested in the functions list. I have…
Need help in formula function
I'm not able to get this formula to function in Smartsheet. The material ordered date is 9/3/2025. Am I missing something to get the date rage to populate? =TEXT([Material Ordered Date]@row +28,"MM/DD/YYYY")&"-"&TEXT([Material Ordered Date]@row +42, "MM/DD/YYYY")
I am trying to find a value in a table based on 2 criteria.
I am trying to find a value in a table based on 2 criteria. One of the values is dynamic from a drop down menu, while the other is static value in a column. The first value is found in column "Building type" and the other is found in Resource type. The table I am referencing is also shown. The result would be shown in the…