Formula that updates % Complete based on Status

swaldon2
swaldon2 ✭✭✭✭
edited 05/16/23 in Formulas and Functions

I need a formula that updates the "Percent Complete" column based on the "Crossing Agreement Status" column.

Owner Not Identified = 0%

Owner Identified = 25%

Owner Contacted = 50%

Owner Deferred = 50%

Agreement in Negotiations = 75%

Agreement Executed = 100%


Example: If the crossing agreement status is marked as owner contacted the percent complete should automatically update to 50%.

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @swaldon2,

    This formula should do what you're after:

    =IF([Crossing Agreement Status]@row = "Owner Not Identified", 0, IF([Crossing Agreement Status]@row = "Owner Identified", 0.25, IF(OR([Crossing Agreement Status]@row = "Owner Contacted", [Crossing Agreement Status]@row = "Owner Deferred"), 0.5, IF([Crossing Agreement Status]@row = "Agreement in Negotiations", 0.75, IF([Crossing Agreement Status]@row = "Agreement Executed", 1)))))

    Sample:

    Hope this helps, but any questions etc. then just post! 😊

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @swaldon2,

    This formula should do what you're after:

    =IF([Crossing Agreement Status]@row = "Owner Not Identified", 0, IF([Crossing Agreement Status]@row = "Owner Identified", 0.25, IF(OR([Crossing Agreement Status]@row = "Owner Contacted", [Crossing Agreement Status]@row = "Owner Deferred"), 0.5, IF([Crossing Agreement Status]@row = "Agreement in Negotiations", 0.75, IF([Crossing Agreement Status]@row = "Agreement Executed", 1)))))

    Sample:

    Hope this helps, but any questions etc. then just post! 😊

  • swaldon2
    swaldon2 ✭✭✭✭

    That worked, thank you!

  • swaldon2
    swaldon2 ✭✭✭✭

    That worked, thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!