How to specify a row has a specific parent in a function

eLIZo

I'm trying to average a collection of rows that fit a certain criteria.

So far, I have this formula and I know it works up until Job@row.

=AVG(COLLECT({DurationCPT},{CPTQ}, Job@row,{Packaging JobCPT},(PARENT("CMO")="Packaging Summary")))

For the rest of the formula, I am trying to full specific CMO rows that have "Packaging Summary" as its parent from my criterion range. Pic below:

Does anyone have any tips for this?

SmartLew

Hey!

How about a helper column using =parent?

This will mirror the parent for that row and you can use this as your criterion range?

SmartLew

Hey!

How about a helper column using =parent?

This will mirror the parent for that row and you can use this as your criterion range?

eLIZo

https://community.smartsheet.com/discussion/comment/340934#Comment_340934

Thanks, that worked! I created a helper column with a column formula of =PARENT([Packaging Job]@row) + " " + [Packaging Job]@row. Then, I created a new sheet reference and this is the finalized formula:

=AVG(COLLECT({DurationCPT}, {CPTQ}, Job@row, {HelperCPT}, "Packaging Summary CMO"))

SmartLew

Glad you got it working!