% Complete Report using boxes specific to create report

Options

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

Tags:

Best Answer

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    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.)


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!