% Complete Based on Child Conditions Met

Kaleb

Smartsheet Community:

I Need to Add to The Formula Below to Calculate % Complete Only if it is a Pass, Fail, or Not Applicable.

=ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100, 0) + "%"

Below is a Screen Shot of what I am Trying to Accomplish.

Thanks

Paul Newcome

You would use an IF statement like so:

=IF(OR(............................), run_%_formula)

=IF(OR([Test Status]@row = "Pass", [Test Status]@row = "Fail", [Test Status]@row = "Not Applicable"), ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100, 0) + "%")

Kaleb

https://community.smartsheet.com/discussion/comment/343771#Comment_343771

I am Receiving Circular Reference

Kaleb

https://community.smartsheet.com/discussion/comment/343795#Comment_343795

I Removed the 1 and Change the Count if to Count because the Original Formula was Looking for a Checkbox. However, I am Still Receiving Circular Reference.

Modified Formula to Look For & Calculate For Only For Pass, Fail, and Not Applicable:

=IF(OR([Test Status]@row = "Pass", [Test Status]@row = "Fail", [Test Status]@row = "Not Applicable"), ROUND(COUNTIF(DESCENDANTS() / COUNT(DESCENDANTS()), COUNT(DESCENDANTS()) / COUNT(DESCENDANTS())) * 100, 0) + "%")

Paul Newcome

Where exactly are you putting the formula, and do you mean that the COUNTIF portions should only take into account those different Statuses?

Kaleb

https://community.smartsheet.com/discussion/comment/343803#Comment_343803

The Formula will Be in the Test Status Column Row 1 (Parent Cell). It Should Count All The Status and Calculate The Total %. Therefore if Any of Those Statuses (Pass, Fail, Not Applicable) Appear, The Formula Should Count it and Display The %

The Question I am Answering is What % of Test have Been Executed and the Those 3 Status is The Criteria for Completion.

Kaleb

https://community.smartsheet.com/discussion/comment/343814#Comment_343814

@Paul Newcome I have Been trying to Adjust this Formula to Look at the Child Statuses to Calculate the % Complete, but Unsuccessful. Any idea what I am doing wrong?

Kaleb

@Paul Newcome I Think I am Close. I am Counting All Children or Descendants that are Not Blank and it Should Calculate % Complete for Pass, Fail, & Not Applicable Statuses Only. Can you Take a look at my Formula and See why I am Still Getting Circular Reference Below?

=IF(OR([Test Status]@row = "Pass", [Test Status]@row = "Fail", [Test Status]@row = "Not Applicable"), ROUND(IF(COUNTIF(DESCENDANTS(), @cell = "Pass", @cell = "Fail", @cell = "Not Applicable") / COUNT(DESCENDANTS()) <> "", COUNTIF(DESCENDANTS(), <>"") / COUNT(DESCENDANTS()), 0) * 100, 0) + "%")

As Reference My Original Formula is Calculating Based on the Child or Descendant Checkbox:

=ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100, 0) + "%"

Paul Newcome

If you are putting the formula in the [Test Status] column then you cannot reference [Test Status]@row.

Kaleb

https://community.smartsheet.com/discussion/comment/344266#Comment_344266

You Are Right! I Should Change it to Children and @row to @ cell, correct?

Paul Newcome

You don't need the @cell reference with hierarchy based functions. You should be able to just use CHILDREN and DESCENDANTS.

Kaleb

https://community.smartsheet.com/discussion/comment/344295#Comment_344295

OK, like this?

ROUND(IF(COUNTIF(DESCENDANTS(), "Pass", "Fail", "Not Applicable")) / COUNT(DESCENDANTS()) <> "", COUNTIF(DESCENDANTS(), "Pass", "Fail", "Not Applicable") / COUNT(DESCENDANTS()), <>"") * 100, 0) + "%")

Paul Newcome

Yes, but the criteria that you are wanting to count for needs to be nested in an OR statement.

OR(@cell = "Pass", @cell = "Fail", @cell = "Not Applicable")

Kaleb

https://community.smartsheet.com/discussion/comment/344723#Comment_344723

Received Incorrect Argument:

=ROUND(IF(COUNTIF(DESCENDANTS(), OR(@cell = "Pass", @cell = "Fail", @cell = "Not Applicable") / COUNT(DESCENDANTS()) <> "", COUNTIF(DESCENDANTS(), OR(@cell = "Pass", @cell = "Fail", @cell = "Not Applicable") / COUNT(DESCENDANTS()), <>"") * 100, 0) + "%"))

Paul Newcome

You will need to adjust your closing parenthesis. You are closing out the OR function but not the COUNTIF.

Kaleb

https://community.smartsheet.com/discussion/comment/344751#Comment_344751

I did and Still Received an Error