automate RYG balls

I'm a smartsheet newbie and i'm ultimately trying to build a formula that automates task progress based on the following:

If % Complete = 100, return Blue

If it’s not scheduled to start yet, return Clear

If it’s past the scheduled finish date, return Red

If % complete >= (today date-start date)/task duration, return Green

Otherwise, return Yellow.

After researching if statements i wanted to at least start with the first simple if statement to return blue ball if task is 100% complete. Below is what i used for the formula but nothing returns in the cell.

=IF([% complete]1 = 100, "Blue")

attached screenshot

RYG task indicator automation.PNG

Find more posts tagged with

Formulas

Comments

Brian W

You're very close. In formulas, % complete is a range of 0-1, so it should be:

=IF([% complete]1 = 1, "Blue")

If you were looking for over 50% it would look like this:

=IF([% complete]1 > .5, "Blue")

Let me know if you need help with the rest of the formula.

RawRobb

Thank you!! I’m obsessed w making this formula work and figure if i chunk out the pieces then I can just tie it all in after I test each piece. This was the easiest piece. Ok. Well I’m onto the next piece. Hope you’re online for my next question! Really appreciate the quick response.

RawRobb

I figured it out, here's the breakdown so that my RYG status indicator is automated based on the below, and you only have to put in your manual % completes. Not bad as i've never used smartsheet and i am basic excel user, never used MS project

For non-zero duration tasks
- If % Complete = 100, return Blue
  - =IF([% complete]1 = 1, "Blue")
- If it’s not scheduled to start yet, and % complete is blank, return Clear
  - =IF(AND(TODAY() < [Start date]7, ISBLANK([% Complete]7)), "")
- If it’s past the scheduled end date and % complete is less than 100%, return Red
  - =IF(AND(TODAY() > [End date]2, [% Complete]2 < 1), "Red")
If % complete < the prorated expected completion (If the percent complete is less than the number of days since the task was scheduled to begin, divided by the task duration, it will be Yellow; otherwise, it will be Green.
- - =IF([% Complete]3 < (TODAY() - [Start date]3) / Duration3, "Yellow", "Green")
All of the above combined
- - =IF([% Complete]10 = 1, "Blue", IF(AND(TODAY() < [Start date]10, ISBLANK([% Complete]10)), "", IF(TODAY() > [End date]10, "Red", IF([% Complete]10 < (TODAY() - [Start date]10) / Duration10, "Yellow", "Green"))))

Brian W

Nicely done. Those nested IF statements can get tricky.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…