Smartsheet Tips: How to Automate RYG Balls

Blog

Smartsheet Tips: How to Automate RYG Balls

Time to break out those formula skills again!  This week, we’ve got another slick “nested IF” formula for you to use to automate Red-Yellow-Green symbols.  Commonly referred to as “those handy RYG balls,” these symbols are often used to indicate if a task is on time (Green), approaching its due date (Yellow), or overdue (Red).  

Instead of manually reviewing and updating status colors, we can use a formula in the symbol column to reflect the status in relation to today’s date and the task’s Due Date.

(If you haven’t yet, check out our post on Nested IF formulas and Harvey balls... we love making life easier with automation and formulas.)

Here’s a quick review of some IF formula basics:

  • The basic IF() syntax (the structural pattern in the formula) is: IF(logical_test, value_if_true, value_if_false).
  • Nested formulas are multi-layered and communicate with multiple columns in your sheet.  Each piece of this communication is nested within the formula.
  • As you build nested formulas like these, we recommend testing smaller sections of the formula before moving on to the next stage, to make sure everything is working properly.

Here’s the sheet we’ll use for this example:

In our process, a Red ball shows that we’re past the due date for a task, Yellow means that we’re within three days of the due date for a task, and Green means the task is more than three days out.  

What you want to do is build a formula so if a task is not complete, the RYG ball will reflect Red, Yellow, or Green depending on how close the task is to its due date.

  • Start with the basic syntax for an IF statement – IF(logical_test, value_if_true, value_if_false)
  • Update the formula so that it checks to see if a task is marked complete: =IF(Complete3 <> 1, "True").  Now apply the formula to the Status column of your sheet.
    • This formula looks to see if the Complete column is unchecked yet (a completed checkbox = 1).  If the checkbox has not been checked in the Complete column, the formula will add the text “True” in the cell.
    • Notice that we don’t have a comma followed by a value_if_false built into this first step in the formula.  You can leave this off of the formula if you don’t want it to take any more steps.  
  • Now, update the formula to check to see how close the task is to its due date, relative to today’s date, and replace “True” with the formula logic that sets the appropriate ball colors.
    • First, build the code for Red balls. How do you tell your sheet if a task is past its due date?  By looking to see if today’s date minus the due date is a positive number.

=IF(Complete3 <> 1, IF(TODAY() - [Due Date]3 > 0, "Red", "Next IF"))

NOTE: “Next IF” is added here as a placeholder for the steps for Yellow and Green balls.

  • Lastly, replace “Next IF” with code to record a Yellow ball if we’re within three days (or on the due date).  Otherwise, a Green ball will appear to reflect that we have three days or more to complete that particular task.

=IF(Complete3 <> 1, IF(TODAY() - [Due Date]3 > 0, "Red", IF(TODAY() - [Due Date]3 > -3, "Yellow", "Green")))

All it takes is a little bit of time working with formulas to automate the processes you have in your sheets – then let your sheet do the work!

To learn more about formulas in Smartsheet, be sure to check out our Smartsheet Formula Examples sheet in the Template Gallery or watch this quick video.

 

 


Til next week, dig in and explore!

- Support Team

Comments

This is a load of ****

The if function only writes the text 'red' or 'green'.. It doesnt perform a ryg ball.. how do I apply the ball effect ?

Re:

Kelly Anthony's picture
Hi Mustafa, Capitalization matters in this formula, so you'll have to write 'Red' or 'Green' in the formula to get the ball effect. Please email us at [email protected] if you're still running into challenges, happy to help out! Thanks, Kelly

It works perfectly

I just tried it exactly like the example and it worked perfectly. One little tip though...set up your Status column as a text field first. Once you see the formula working and changing your values to Red, Yellow or Green in word form? Edit column properties and change to symbols. They turn to balls and change color accordingly. To the author of this tip thanks very much. I love it and it's very helpful.

RE: It works perfectly

Smartsheet Community Manager's picture
Glad to hear it Jason! Thank you for the tip. - The Smartsheet Team

RYG Balls

Hello, I can't get this formula to work. Are there a basic set of columns I need to use in order for this to apply?

RE: RYG Balls

Smartsheet Community Manager's picture
Hi Razmig, this formula should be applied to a symbol column. Please contact [email protected] if the formula does not work. Thank you - The Smartsheet Team

Missing "yellow" in the formula

I can't seem to get the "middle" "if statement" to work. The formula I have gives me red and green, but i can't get yellow in the "middle". Can you help me with this formula? =IF(COUNTIF(CHILDREN(), "Red") >= 0.4, "Red", IF(COUNTIF(CHILDREN(), "Red") >= 0.8, "Yellow", "Green"))

Re: missing "yellow" in the formula

Kelly Anthony's picture
Hi there, Please email us at [email protected] so we can help you with this formula. A count formula will only count rows, so it looks like the decimals you have here may be out of place. Thanks! Kelly

Question regarding column property

I would like to add another color, say a grey circle/ball along with RYG to denote it as "not applicable", is it possible?

Re: Question regarding column property

Kelly Anthony's picture
Hi Madhan, At this time, just the three RYG balls are available, though I've shared your suggestion with our product team to consider in future Smartsheet enhancements. Thanks! Kelly

Madhan,

Madhan, I created a ACTIVE flag column, so for my projects if a task is N/A I remove the flag. Then, I used the RGYGray balls and made my formula if done=gray or if "not active" also = gray. =IF(Active13 = 0, "Gray", IF(Complete13 = 1, "Gray", IF(TODAY() - [Due Date]13 > 0, "Red", IF(TODAY() - [Due Date]13 > -7, "Yellow", "Green"))))

Automate RYG balls

This is useless, you are assuming so much prior knowledge. If I want to apply this rule to the whole column where is my starting point? The column title, do I cut and paste as in Excel. I am very new to formula and this is useless for a complete beginner. This rule would be so useful but bear in mind those of us with no prior knowledge or limited skills.

Re: Automate RYG balls

Kelly Anthony's picture
Hi Kay, You're right, we do jump into the formula deep-end with this post -- for some of the basics, this article from our Help Center is a great starting place: http://help.smartsheet.com/customer/portal/articles/775363-using-formulas. The video in this article will show you where to insert formulas in your sheet and walk through a few basic examples. If you'd like more help, please reach out to our Support Team at [email protected] They're formula experts (and the authors behind this post!) and happy to help you out. Hope this is helpful, please let us know if you need an extra hand. Thanks, Kelly

RYG + Blue balls

Hi - It is commonplace to use Blue balls to indicate Completed tasks, vs Green which are those still in progress. Could Blue balls be added to the symbol selection? Kind regards, Mike

Re: RYG + Blue balls

Kelly Anthony's picture
Hi Mike, Thanks for your feedback -- I've passed your request along to our product team to consider. We're looking for new visual symbols to add to Smartsheet to make tracking progress and status easier. Thanks, Kelly

Invalid data response

Hi, I got this to mostly work first time but in a few fields I am getting "#INVALID DATA TYPE" returned for a bout 5% of rows. Not sure why. These fields are the same as the ones above and below. Can you please assist? Thanks

Re: Invalid data response

Kelly Anthony's picture
Hi Ray, Please email us at [email protected] so we can lend a hand and help get to the bottom of what's going on. Thanks! - Kelly

Keep Status Ball Green when Checking Complete

Is there a way to add to formula so when the Complete checkbox is checked, the Status ball remains visible and green, instead of disappearing? I've been trying without any luck. Thx.

Re: Keep Status Ball Green when Checking Complete

Kelly Anthony's picture
Hi Tom, The status ball should remain, and stay green. Can you please email us at [email protected] so we can lend a hand? Thanks! Kelly

Hi Tom. I was having the same

Hi Tom. I was having the same issue. I found you need to add a bit more to the end of the formula to get the green ball to stay when checked. My formula looked like this (different column names). I know this is a late reply but maybe it'll help someone else with the same issue =IF(Complete22 <> 1, IF(TODAY() - End_Date22 > 0, "Red", IF(TODAY() - End_Date22 > -3, "Yellow", "Green")), IF(Complete22 = 1, "Green"))

Using a Flag Instead

Is it possible to write the formula using a flag instead? Also can the formula be written strictly off of the due date and not a completed box?

Re: Using a Flag Instead

Kelly Anthony's picture
Hi Sara, You can use a flag rather than RYG balls in a similar scenario, but the formula will be a bit different, since the Flag symbol is driven by boolean values, rather than the three options you have with RYG balls. Please email us at [email protected] so we can learn a bit more about how your sheet is set up and help you with the correct formula. Thank you! Kelly

Formula in Status Column using Harvey Balls

If able, please assist me in writing my formula to populate the Status column with Harvey Balls. To begin with, I am trying to the first step of the formula in which I want to place a green harvey ball in the status column if the box is checked in the Actual Completion column. You are able to see my attempt in row 19, which has the checked box and would be a good test for my formula. I have tried wrinting the first step in several ways but I am getting the error #UNPARSEABLE.

Re: Formula in Status Column using Harvey Balls

Kelly Anthony's picture
Hi Mandy, Our team will gladly help, we'll just need to know a bit more about how your sheet is set up. Please email us at [email protected] so we can lend a hand. Thanks, Kelly

Apply to entire column?

Hi, This is a great help. Is there a way to set it up so that when I add new blank rows to my sheet, this formula is automatically included in my selected column? I'm afraid I'll forget to copy and paste the formula each time I insert a row. Thanks, Erin

RE: Apply to entire column?

Emily Esposito's picture
Hi Erin, we have an auto-fill feature that should automatically insert the formula. Here's an article that will walk you through the steps: http://help.smartsheet.com/customer/portal/articles/1641473. If it isn't working, please email [email protected] for more help. Thank you! Emily

IF statement for range of values

Hi Erin, I'm trying to set-up an if statement for a range of values. For instance if a cell has a value bigger than 0, but smaller then 10, i want it to turn yellow. How do I do it? Cheers, Evert.

RE: IF statement for range of values

Emily Esposito's picture
Hi Evert -- We'd be happy to walk you through these steps! Please email Support at [email protected] and a member of our Support team will be able to assist with this! Best, Emily

Use % complete Instead of complete box

I was wondering if you could use the % Complete field instead of the Complete check box in the formula? Thanks

RE: Use % complete Instead of complete box

Emily Esposito's picture
Hi Kim -- Yes, just replace complete3 with the % Complete reference. It will look like this: [% Complete]3. The rest is the same. Best, Emily

That worked like a charm!

That worked like a charm! Thanks I have one more question...can you change the timeframe for the Yellow? Since, I am using this at the milestone level I want more than 3 days heads up. Maybe something in the 15 day range. Can that be done? Thanks

RE: That worked like a charm!

Emily Esposito's picture
Hi Kim -- Yes, it's easy! Just change the "3" to a "15" in the above formula. If you run into any issues, please email our Support team at [email protected] -Emily

Difficulty getting the yellow ball to work.

I have successfully implemented the "Green" and "Red" balls, however I cannot get the "Yellow." So far I have: =IF([Grand Total]4 = [Amount Received]4, "Green", IF([Amount Received]4 <> [Grand Total]4, "Red", IF([Amount Received]4 = 0, "Yellow"))) My thinking is when a value comes back "0," it will be tagged with a yellow bulb. Can you help me? Thank you.

RE: Difficulty getting the yellow ball to work.

Smartsheet Community Manager's picture
Hi John, it will be helpful to know more about your sheet so please contact the Support Team at [email protected] to help figure out the formula needed. All the best, The Smartsheet Team

I'm trying to figure if it

I'm trying to figure if it would be possible to to trigger a date column on the basis on the RYG balls in another column. For instance, when we are done with a request we put up the green ball. Can a column be structured so that the date and time when the ball is put up reflects?

RE: I'm trying to figure if it

Smartsheet Community Manager's picture
Hello Megha, it sounds like you would like to show a timestamp of when the ball was selected. While we do not have this exact feature, there are a couple options. If this is the LAST thing to change in the row, you can use the Modified (Date) column which shows a timestamp of when the row was last modified. You can also view the history of the cell by right clicking in the cell and selecting View History. This will show who made the change, when it was made, and what the change was. A better solution may be to work with an integrated third party app, Azuqua, which can do this. https://azuqua.com/integrate-smartsheet Thanks! - The Smartsheet Team

RYG Based on % complete (Noob)

Been trying to understand the RYG and IF command or automating the balls based on a % complete. Simply 0-10%=Red, 11-99%=Yellow and 100% =Green I have a column called % complete with numbers ranging from 0 to 100 and want the balls as a quick visual check. If someone could assist with how to understand the formula and how to change the range and if the same theory could be applied to a dropdown list of Complete, Commenced and Not Started. Thanks in advance

RYG color change of next Step

I use RYG to change status of each step. If one collaborator completes a task and turns the symbol Green, can next step (cell below in next row) be turned from Gray to Yellow automatically, which means it is due for the next person to who that task is assigned.

Status change is note field is blank...

I've tried everything to get my status column to change to alert me to when someone adds a note our note column. Can you or anyone help!

Status change is note field is blank...

Diana Ramos's picture
Hi Derek, One thing you can do is set up a notification to alert you any time someone makes changes to your 'note' column. Here is a quick article on how to use notifications: http://bit.ly/2mknSBe. Hope this helps. Let us know if you have any further questions. Thanks, Diana

Add new comment