What is wrong with my formula?

jerry123

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!

SteyJ

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

JamesB

@jerry123

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))))

jerry123

Hi everyone!

Thank you for this,

@JamesB & @SteyJ

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)), "")