Percent complete of a checkbox row

Options

I want to take the Child rows and put a percent complete in the row I'm calling Grandparent I want it to be based off the child rows because the amount of Child rows in each parent group differ. So I want the percent complete to be waited off the over all check boxes of the Child rows


Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Chris Nicklaus

    Since Parent 1 and Parent 2 are are children of your Grandparent row, you could use the CHILDREN function to get an Average of the percents below, ex:

    =AVG(CHILDREN())

    However, you mention a weighted percent. It sounds like you want to count up all the checkboxes under the Grandparent, separate from a different Grandparent (so not the entire sheet).

    If so, I've come up with one way to potentially do this. Someone else may have a more concentrated idea, but for me the easiest way to achieve your goals would be to create two helper columns.

    1 . COUNT column

    The first helper column would simply return the number of children, using this COUNT formula:

    =COUNT(CHILDREN([Primary Column]@row))

    This would be through the entire column except when you have Grandparent Rows. The Grandparents would have a SUM of the children, so SUMMING the number that each Parent returns.

    =SUM(CHILDREN())


    2 . COUNTIF Column for checks

    The second helper column would be similar, but instead of just counting children, it would count how many children in the checkbox column were checked, or 1, using COUNTIF.

    =COUNTIF(CHILDREN(Checkbox@row), 1)

    Then again, in the Grandparent row, you would have =SUM(CHILDREN()) adding together those checks.


    3 . Final Formula in Checkbox Column

    This would then allow you to take these values and create a percentage in your grandparent row:

    =(Checked@row / Total@row) * 100 + "%"


    Example sheet:


    You can then hide those extra columns so they're not visible in the sheet itself.

    Let me know if this works for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!