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!



  • SteyJ
    SteyJ ✭✭✭✭✭
    edited 08/15/23

    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


    Jacob Stey

  • JamesB
    JamesB ✭✭✭✭✭✭


    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
    jerry123 ✭✭✭✭

    Hi everyone!

    Thank you for this,

    @JamesB & @SteyJ

    I used this formula and looks like it works too!!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!