Help rolling up formula but skipping the calculation in rows without a Proof Status

jbarn276

Hello! Having some trouble with a formula and not sure what I'm doing wrong or if there is any other way of going about getting the result i'm looking for. Basically, I have a sheet with 28 children rows underneath a parent row and using the Proof function and the Proof Status column I want to calculate % complete of each child row and then roll that % complete of each row up to the parent row. However, if there is not a proof that is created in a child row (thus no proof status) I need the calculation to skip or leave that cell blank so the roll up is accurate. In other words, using the screen snip as the example, if Proof Status is blank at the row, then there should be no percentage in the Property Completion % column. Thus, my roll up % would accurate to the number of proofs status vs. calclating all children lines and ending up with the 9%.

The column formula I currently have is the following:

=IF(ISBLANK([Proof Status]@row), AVERAGEIF(CHILDREN(), "<>"), IF([Proof Status]@row = "Created", 0.2, IF([Proof Status]@row = "In Review", 0.6, IF([Proof Status]@row = "Requires Changes", 0.4, IF([Proof Status]@row = "Approved", 0.8, IF([Proof Status]@row = "Completed", 1, ""))))))

Thanks in advance for any advice on what I can do to make this work properly.

Duaer Team

Your first check, ISBLANK([Proof Status]@row), is also TRUE on child rows that have no status, so those rows go into the roll-up branch instead of staying blank. Checking whether any child row below has a value avoids that:

=IF(COUNT(CHILDREN()) > 0, AVG(COLLECT(CHILDREN(), CHILDREN(), ISNUMBER(@cell))), IF([Proof Status]@row = "Created", 0.2, IF([Proof Status]@row = "In Review", 0.6, IF([Proof Status]@row = "Requires Changes", 0.4, IF([Proof Status]@row = "Approved", 0.8, IF([Proof Status]@row = "Completed", 1))))))

Rows whose children have values average only the numeric values below them; other rows use the status, and the last IF has no "false" value, so a row with no status is left blank. I haven't tested it on your sheet, so try it on a copy first.

Disclosure: I'm on the Duaer team; this was drafted with AI help and checked against Smartsheet docs.