% Complete Report using boxes specific to create report

imtonydillard

I use the Board Report Column as a filter only. More specifically, it allows me to filter only the items they want to see on their report. In addition, I need to provide them with a "% Complete" of only those items using based on the following: Not Applicable needs to be excluded, Not Started needs to be 0% and Complete needs to be 100%.

How do I do this

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317876#Comment_317876

You have two hierarchy levels in your first screen shot. Using my formulas above in a "% Complete" column, the parent rows at both levels would display a percentage complete for the child rows underneath them, and all the child rows would show either 0% (for Not Started) or 100% (for Complete.)

Jeff Reisman

With a % Complete column, we'll use nested IF statements to assign your values to the checked rows. Since you have a hierarchy, we'll use a CHILDREN function and a DESCENDENTS function in here too, and through in AVG/COLLECT for good measure. Make sure your % Complete column is formatted for %:

I am assuming you want this broken down by the overall Phase (Phase 1, Phase 2, etc,) and also by individual Phases (Sales, Network, License, etc.)?

On the child rows for your the smallest Phases (Sales, etc), in the % Complete column, use:

=IF([Board Report]@row, IF(Status@row = "Complete", 1, IF(Status@row = "Not Started", 0, ""), "")

In English, if the board report column is checked, consider if Status column equals Complete, and if it does, set this value to 1; otherwise, consider if Status column equals Not Started, and if it does, set this value to 0; if it's any other value, or if Board Report column is unchecked, leave the cell blank.

On your Phase parent rows for Phase 1 Sales, etc, in the % Complete column, use:

=AVG(COLLECT(CHILDREN([% Complete]@row), [Board Report]:[Board Report], 1))

In English, give me the average % Complete of the children of this subphase, where Board Report is checked.

On the over Phase row, in the % complete column, use:

=AVG(COLLECT(DESCENDENTS([% Complete]@row), [Board Report]:[Board Report], 1))

In English, give me the average % Complete of all the descendants of this Phase, where Board Report is checked.

imtonydillard

@Jeff Reisman

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317876#Comment_317876

You have two hierarchy levels in your first screen shot. Using my formulas above in a "% Complete" column, the parent rows at both levels would display a percentage complete for the child rows underneath them, and all the child rows would show either 0% (for Not Started) or 100% (for Complete.)

imtonydillard

Mega thanks @Jeff Reisman .