Referencing % in Formula in Symbol Column (RYG)

lisa. caldwell37926

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.

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", " "))))