What is wrong with my formula?
Hi all,
I'm trying to write a formula with the following conditions to get the % Complete
Complete  100%
N/A 100%
Outstanding  0%
Partially Complete  50%
for example, if we have 4 tasks  2 of them are N/A and 2 of them are complete  it should be 100% since N/A and complete acts as 100%
if we have 2 partially complete and 2 complete  it should be 75% complete since Partially Complete acts as 50%  making it 300/400 = 0.75*100% = 75%
This is my formula
=IF(COUNTIFS(CHILDREN(), <>"N/A", CHILDREN(), <>"") < 0, 0, IF(COUNT(CHILDREN([Task Description]@row)) = COUNTIF(CHILDREN(), ""), 0, IF(COUNT(CHILDREN([Task Description]@row)) = COUNTIF(CHILDREN(), "N/A"), 1, (COUNTIFS(CHILDREN(), "Complete", CHILDREN(), <>"N/A") + COUNTIFS(CHILDREN(), "Partially Complete", CHILDREN(), <>"N/A") * 0.5) / (COUNT(CHILDREN([Task Description]@row))  COUNTIF(CHILDREN(), "N/A")))))
Using this formula when I have 4 tasks  1 complete, 2 N/A, 1 Outstanding my % complete shows as 50% when it actually should be 75%.
What is wrong with my formula? Your help is greatly appreciated!
Answers

Would it be easier to use % Complete as a number value?
=AVERAGEIF(CHILDREN([% Complete]@row), <>"")
You can also assign text to the cell by using nested IF statements, or use a helper column to have the desired text per %. I usually just do conditional formatting for this, though.
Hope this helps
Sincerely,
Jacob Stey

Using your comments above it seemed like you were trying to get a percentage based on how many were not at 0%, I also used a % complete column with a column formula measuring percentage but not as an average. This formula will set the percent complete based on the following rules.
If a task is a parent row, then count the children greater than 0% and divide it by the total number of children. If it is a child row, set the percentage based on your parameters above.
=IF(COUNT(CHILDREN()) > 0, SUM(COUNTIF(CHILDREN(), >0.1) / COUNT(CHILDREN())), IF(OR(Status@row = "Complete", Status@row = "N/A"), 1, IF(Status@row = "Outstanding", 0, IF(Status@row = "Partially Complete", 0.5))))

Hi everyone!
Thank you for this,
I used this formula and looks like it works too!!
=IFERROR(SUM(COUNTIF(CHILDREN(), ="Complete") / COUNT(CHILDREN()), COUNTIF(CHILDREN(), ="N/A") / COUNT(CHILDREN()), (COUNTIF(CHILDREN(), ="Partially Complete") / COUNT(CHILDREN()) * 0.5)), "")
Help Article Resources
Categories
Check out the Formula Handbook template!