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.
Comments
-
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!
-
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", " "))))
Help Article Resources
Categories
Check out the Formula Handbook template!
