Complicated if statement - health column

Hi All,

I'm really hoping you can help me.

We have a project tracker that has below columns:

Client Type (platinum, gold or silver), Delivery Date, Aging, Status, Health.

The health column is a symbol column with green, yellow and red colours. We need the formula to indicate the below requirements:

I've been trying for days now but can't come up with such formula so any help would be much appreciated here please!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you entering the priority as well or just the priority description? Are you able to provide a screenshot of the source data (sample data can replace confidential data as needed)?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 07/06/23

    Hi @angelapaj,

    Try this:

    =IF(Priority@row = 1, "Red", IF(Priority@row = 2, "Red", IF(Priority@row = 3, "Yellow", IF(Priority@row = 4, "Yellow", IF(Priority@row = 5, "Yellow", IF(Priority@row = 6, "Green", IF(Priority@row = 7, "Green", IF(Priority@row = 8, "Green", IF(Priority@row = 9, "Green", IF(Priority@row = 10, "Green", IF(Priority@row = 11, "Green", IF(Priority@row = 12, "Green", IF(Priority@row = 13, "Green", "")))))))))))))

    You can simplify this further, but leaving like this will make it easier to edit later if you need to adjust.

  • angelapaj
    angelapaj ✭✭✭✭

    @Paul Newcome I have only been entering the priority description.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah. I think I see what is going on. In the screenshot of your table "Gold & Aging & Review" is not an actual status. It is a combination of columns you want to evaluate to output the color? If that is the case, which column is "Aging", and how do we determine yes/no?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!