% Complete is Incorrect

MelM18
MelM18 ✭✭✭
in Formulas and Functions

In project settings I have dependencies enabled. This auto calculates % complete; however, the. percentage is incorrect.

In my example I have 2 Parent rows, each with 5 tasks. Everything under Parent 1 is complete and everything under Parent 2 is not started. The % complete should be 50%; however it shows 43% complete.

I am able to determine the expected complete using this formula:

=IF(Status@row = "Pending", 0, IFERROR(AVG(CHILDREN()), IF([End Date]@row <= TODAY(), 1, IF([Start Date]@row >= TODAY(), 0, IF([Start Date]@row <= TODAY(), NETWORKDAYS([Start Date]@row, TODAY()), 0) / NETWORKDAYS([Start Date]@row, [End Date]@row)))))

My goal is to compare the % Expected Complete against the (actual) % Complete columns to identify where we have a variance.

Screenshot 2024-04-25 at 5.32.50 PM.png

Thoughts?

· Share on FacebookShare on Twitter

Best Answer

  • MelM18
    MelM18 ✭✭✭
    Answer ✓

    Ha! I was able to figure it out… It takes duration into consideration. Since the Parent 1 Tasks have a duration of 18 days and my Parent 2 Tasks have a duration of 24 days, they are weighted differently.

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!