Harvey Ball Percentage with Multiple Selection Dropdown Statuses

Using the drop down selections in a Status column as the reference, i've set up a Completion column to indicate how far along a project is.


However, occasionally multiple statuses are needed to appropriately indicate the project's level of completion. I've tried multiple iterations of the below formula to varied degrees of failure. Some arrangements net "Unparseable". Others net "Invalid Data Type". And some do not trigger an error message, but also do not populate the Completion column.


..., STATUS@row = "output" + "invoice"), "Full")))))

Answers

  • Sean Morgan
    Sean Morgan Employee
    edited 12/05/20

    Hello @Damon Lloyd ,

    Depending on your Sheet layout, columns used etc, will determine the Formula to use.

    When accounting for multiple criteria in a multi select dropdown, its best to use the AND + HAS functions.

    Although the below may not be fully accurate, it should present how to use the above functions:

    The formula I used was =IF(AND(HAS(Status@row, "Output"), HAS(Status@row, "Invoice")), "Full")

    Let me know if you have any questions!

    Kindest Regards

    Sean

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!