% Complete

I am trying to do a column where it calculates the % complete based on 4 other columns (that have to be completed in succession). Once a date is entered into these other columns then I would like the % complete to add 25% to the % complete.

I was thinking I need to do an Multiple IF statement, but cannot seem to figure out the formula.

Or if someone knows an easier way!

Best Answer

  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭
    Answer ✓

    This formula would work assuming that the milestones are completed in sequence. Technically, it will allow you to "skip" a milestone. Meaning if don't add dates to Milestones 1-3, but there is a date in Milestone4 it will still result in 100%.

    =IF(ISDATE([Milestone4]@row), "100%", IF(AND(ISDATE([Milestone3]@row), NOT(ISDATE([Milestone4]@row))), "75%", IF(AND(ISDATE([Milestone2]@row), NOT(ISDATE([Milestone3]@row))), "50%", IF(AND(ISDATE([Milestone1]@row), NOT(ISDATE([Milestone2]@row))), "25%", "0%"))))

Answers

  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭
    edited 12/13/21
  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭
    Answer ✓

    This formula would work assuming that the milestones are completed in sequence. Technically, it will allow you to "skip" a milestone. Meaning if don't add dates to Milestones 1-3, but there is a date in Milestone4 it will still result in 100%.

    =IF(ISDATE([Milestone4]@row), "100%", IF(AND(ISDATE([Milestone3]@row), NOT(ISDATE([Milestone4]@row))), "75%", IF(AND(ISDATE([Milestone2]@row), NOT(ISDATE([Milestone3]@row))), "50%", IF(AND(ISDATE([Milestone1]@row), NOT(ISDATE([Milestone2]@row))), "25%", "0%"))))

  • Thank you Jason. It worked!

    😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!