Parent & Child Formulas in the Same Column?

My sheet is structured as a sort of tiered task checklist. There are several Parent rows with varying numbers of Child rows. There are two columns that are involved with my issue: the first is a "Status" column, and the second is a sort of "True/False" binary check labelled as "% Complete."

There are no formulas in the "Status" column and is formatted as Text/Number.

There are two formulas I'm trying to implement in the "% Complete" column that is also formatted as Text/Number (these formulas are being input into the individual row cells):

For the Parent rows: =(COUNTIF([% Complete]2:[% Complete]37, 1) / COUNT([% Complete]2:[% Complete]37))
For all the Children: =IF(Status[row#] = "Complete", 1, IF(Status[row#] = "N/A", 1, 0))

Right after entering the multiple variations of the formulas, everything works fine. But as soon as I save it and go to another sheet, when I return to this sheet, all of the formulas are gone save for the top Parent row.

One possibility I can think of as far as issues is that there are a total of 654 rows being utilized that could be causing a memory threshold issue. Other than that, I cannot for the life of me figure out why this is happening. Am I doing something wrong? Is there a better way of going about this?

Find more posts tagged with

Formulas

columns

parent row

Accepted answers

mcrumley

Solved the issue for the time being. Simply did my original Parent formula in each separate parent category at specific ranges for their children in a separate column to the CHILD and Subtask Complete columns.

UPDATE: It is still overwriting the formulas (save for Row 1), not in my template file, but in the copies I'm trying to fix. I really do not understand what is happening here.

All comments

Paul Newcome

Try combining them into a single formula and applying it as a column formula:

=IF(COUNT(CHILDREN([Primary Column]@row)) = 0, child_row_formula, parent_row_formula)

Kerry St. Thomas

I shouldn't think that a memory threshold issue would be hit with 650 rows of data; sheets have a capacity of 20,000 rows.

One thing I CAN recommend is column formulas. Consider adding a Hierarchy column =COUNT(ANCESTORS()) as a column formula. The parent will return 0; the children will return 1 (unless there are more levels of course). But then you can use IF and CHILDREN to do your counting:

=IF([Hierarchy@row=0, COUNTIF(CHILDREN([% Complete]:[% Complete]),1)/COUNT(CHILDREN([% Complete]),
IF(OR(Status@row="Complete",Status@row="N/A"),1,0))

Then just convert it into a column formula. Save that and see if it saves. If it doesn't save, that sounds like a legitimately weird thing so I'd Contact Support at Smartsheet.

Good luck!

mcrumley

I appreciate the responses! Unfortunately, I have not been able to get either of your suggestions to work. Presently I am attempting to set things up a little differently with a dedicated CHILD column that simply checks if the row is a CHILD or not (implemented as a column formula), and a Task Complete column that returns 1 or 0, and then a third column that is to calculate the percent complete of all subtasks using this formula:

=IF(CHILD@row = 0, COUNTIF(ANCESTORS([Subtask Complete]@row) = 1 / COUNT(ANCESTORS([Task Name]@row)), ""))

But my issue now is that the above formula returns a "#DIVIDE BY ZERO" error. I consider this to be progress as it is not returning the "#UNPARSEABLE" error anymore. lol

I have a feeling that I am using "COUNTIF(ANCESTORS([Subtask Complete]@row) = 1" incorrectly. This is probably checking to see if there is one ancestor or not, not if each ancestor's subtask equals one.

mcrumley

UPDATE: It is still overwriting the formulas (save for Row 1), not in my template file, but in the copies I'm trying to fix. I really do not understand what is happening here.

mcrumley

Turns out a coworker had made an automation process that I was not aware of that would update and item that is marked complete to be set to "100%." Which was replacing my formulas. Deleted that, and things seem to be working just fine. I appreciate everyone's help!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.