Can I use 2 formulas in 1 cell/column?

Hello! I'm stuck trying to put to formulas into one column. Here's the scenario.

I have multiple projects with multiple subtasks.

When the Status column is updated to "Completed" I would like the Progress column to update to 100%.

I've successfully completed this step with the formula:

=IF(Status1 = "Completed", "100%") in the Progress column.

Now, I'd like the average % of the completed subtasks to display in the parent row in the Progress column.

So, if I I have Parent Task 1 with 4 Subtasks, and 2 are moved to Completed, those 2 subtasks should have 100% in the Progress column and the Parent Task should have 50% in the Progress column.

Is this possible?

This is ideally how I'd like it to look.

Find more posts tagged with

Formulas

Accepted answers

Mike TV

@jess_roberts

With some additional fiddling I figured out how to keep the In Progress blank and make it look just like you wanted:

Progress formula (column set as percent):

=IFERROR(SUM(CHILDREN(Progress@row)) / COUNT(CHILDREN(Status@row)), IF(Status@row = "In Progress", "", IF(Status@row = "Completed", 1)))

jess_roberts

Blank is perfect, thank you!!! This is exactly what I needed!!!

All comments

Mike TV

@jess_roberts

I can do it but you need "In Progress" to show 0% for your AVG function to work properly.

Progress column formula (column formatted as %):

=IFERROR(AVG(CHILDREN(Progress@row)), IF(Status@row = "In Progress", 0, IF(Status@row = "Completed", 1)))

Mike TV

@jess_roberts

With some additional fiddling I figured out how to keep the In Progress blank and make it look just like you wanted:

Progress formula (column set as percent):

=IFERROR(SUM(CHILDREN(Progress@row)) / COUNT(CHILDREN(Status@row)), IF(Status@row = "In Progress", "", IF(Status@row = "Completed", 1)))

jess_roberts

Blank is perfect, thank you!!! This is exactly what I needed!!!

jess_roberts

@Mike TV follow up question, if you don't mind. If the Status Column = In Progress, is there a way I can type it in manually?

Mike TV

@jess_roberts

Which column & row are you wanting to manually type something into? Also, what are you wanting to type into whichever location?

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.