% Complete Based on Child Conditions Met
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
Answers
-
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) + "%")
-
I am Receiving Circular Reference
-
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) + "%")
-
Where exactly are you putting the formula, and do you mean that the COUNTIF portions should only take into account those different Statuses?
-
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.
-
@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?
-
@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) + "%"
-
If you are putting the formula in the [Test Status] column then you cannot reference [Test Status]@row.
-
You Are Right! I Should Change it to Children and @row to @ cell, correct?
-
You don't need the @cell reference with hierarchy based functions. You should be able to just use CHILDREN and DESCENDANTS.
-
OK, like this?
ROUND(IF(COUNTIF(DESCENDANTS(), "Pass", "Fail", "Not Applicable")) / COUNT(DESCENDANTS()) <> "", COUNTIF(DESCENDANTS(), "Pass", "Fail", "Not Applicable") / COUNT(DESCENDANTS()), <>"") * 100, 0) + "%")
-
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")
-
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) + "%"))
-
You will need to adjust your closing parenthesis. You are closing out the OR function but not the COUNTIF.
-
I did and Still Received an Error
Help Article Resources
Categories
Check out the Formula Handbook template!
