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

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?

Find more posts tagged with

Formulas

Accepted answers

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?

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.