What is wrong with my formula?

Options

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!

Tags:

Answers

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

    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

  • JamesB
    JamesB ✭✭✭✭✭✭
    Options

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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!