Can I use 2 formulas in 1 cell/column?

jess_roberts

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.

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!!!

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?