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

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.