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.


  • Mike Wilday
    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! 

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!