Formula Error at 100% Completion: How to Display 'Closed' Instead?

This discussion was created from comments split from: Automatic Project Phase Formula.

Answers

  • lyss15
    lyss15 ✭✭

    Hello,

    I have the following formula is working until the whole project is at 100% then we get #INVALID VALUE

    what can I change for one project is total 100% to show Closed

    =INDEX(COLLECT(Task:Task, [% Complete]:[% Complete], AND(@cell <= 0.99, @cell >= 0 ), Hierarchy:Hierarchy, 1), 1)

  • SSFeatures
    SSFeatures ✭✭✭✭✭

    Hi,

    Try wrapping your formula in an IFERROR. If your formula gives you an #INVALID VALUE error then you know that the project is 100% completed, so then display "Closed".

    =IFERROR(
      INDEX(COLLECT(Task:Task, [% Complete]:[% Complete], AND(@cell <= 0.99, @cell >= 0 ), Hierarchy:Hierarchy, 1), 1),
      "Closed"
    )
    

    Hope this helps!

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Automatic sorting, sorting with filters, saving sort settings
    • Spell checking
    • Report PDF generator that supports grouped and summarized reports

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!