Referencing % in Formula in Symbol Column (RYG)

Is there a trick to referencing % Percentages)when using the If function, automating the RYG symbols in a Symbol column? I have a simple IF formula referencing cell formatted to %. When I replace the % with any text and update formula to reflect the change, the correct RYG symbol is displayed. When I attempt to reference the % in cell, no symbol displays.

Find more posts tagged with

Formulas

Support

Comments

Mike Wilday

Actually, yes. There is a trick to working with percentages in formulas. Percentages in Smartsheet are calculated in the backend as decimals, even though they display as 100%. So you need to work with decimals in your formula. .25 = 25% .5 = 50%, and 1 = 100%.

So... in the case of an IF statement for and RYG column. =IF([% column title]23 <= .25, "Red") would return a red if the percentage column was less than or equal to 25%.

Hope that helps!

MichelleMWatkins

Hi Lisa,

If you need to see a formula that is working to automate RYG, here's mine (a row 8):

IF([% Complete]8 = 1, "Gray", IF(AND([End Date]8 < TODAY(), [% Complete]8 < 1), "Yellow", "Green"))

Using RYG and Gray where Gray is my 100% complete. Can swap out Gray for Blue if you are using that option instead.
My business teams have requested that we not automate Red, that this is a manual override after group discussion. So Yellow is my 'past due' ball where normally I would have had red.

For parent rows, if you want it to inherit the state of the children tasks you would use this formula:

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", IF(COUNTIF(CHILDREN(), "Gray") > 0, "Gray", " "))))

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…