Average Percentage for Parent Rows

Paigebrim

My current formula is calculating the average percentage of parent & children rows. How do I get it to look only at level 1 rows? Here is my current formula: =INDEX(COLLECT([% Complete]:[% Complete], Level:Level, 0, [% Complete]:[% Complete], <>0, [% Complete]:[% Complete], <1, [Current Phase?]:[Current Phase?], 1), 1)

thanks!

Genevieve P.

Hi @Paigebrim

This formula looks like it's bringing back a single value, not calculating an average, could that be the case? If you want an Average, you'll need AVG before the COLLECT function:

=AVG(COLLECT([% Complete]:[% Complete], columns and criteria))

In your case, if you only need Level 1, you can swap that out in your current structure:

=AVG(COLLECT([% Complete]:[% Complete], Level:Level, 1, [% Complete]:[% Complete], <>0, [% Complete]:[% Complete], <1, [Current Phase?]:[Current Phase?], 1))

Let me know if that does the trick! If not, it would be helpful to see screen captures of your sheet, but please block out sensitive data.

Cheers,

Genevieve