Calculating a percentage of lines complete based on weight value

Options

I would like to input a formula that shows the percentage of the parts that are complete, based on weight instead of quantity.

My current formula setup is =(SUMIF(CHILDREN(), [Complete]= 1)) / (SUM(CHILDREN()))

I need to know what to type after the [Complete] in order to make the formula parsable. If I put in a number such as 1 (ex [Complete]1 ), it becomes parsable but does not give accurate percentages. 

If the formula was working correctly, it should currently be giving a value of 0.75.


Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Kayla Wootten ,

    Try:

    =SUMIF(CHILDREN([Complete]@row), = 1, Children()) / (SUM(CHILDREN())

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Kayla Wootten
    Options

    Hello @Mark Cronk ,

    Yes it does, thank you!

    I now have a second question.

    We have a smartsheet we use that has multiple parent rows, each with children under them, like the example I gave a snip of. We want to implement this formula for every parent row. What would your thoughts be on that?

    Thank you!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Kayla Wootten ,

    To identify a row as a parent, add a text/number helper column [Tier] with the column formula =COUNT(ANCESTORS()). Parent rows will be =0, Children will be 1 (or greater if you have multiple tiers)

    =IF(TIER@row=0, SUMIF(CHILDREN([Complete]@row), = 1, Children()) / (SUM(CHILDREN()), "")

    Placing this formula in your Complete column is going to prevent users from checking complete in the child rows unless you're using a formula to determine Comlplete. If you're using a formula add it in place of the "" at the end and all will be fine. Otherwise you'll need to create another column to mark parents complete.

    Help?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!