Automated RYG Based on % Complete Formula Help

Susie Wachowski

Hi,

I'm trying to automate the RYG colored balls so they automatically change colors based on the percent complete entered. I watched a video, read through the suggestions from the community, and was unable to come up with a formula that worked correctly. Can you please help me identify the error in my formula? 

When I enter in each element of the formula separately, they work. However, I am unable to combine them successfully. 

Separately: 

=IF([% Complete]3 = 1, "Green"), 

=IF([% Complete]3 <= .50, "Red"),

=IF([% Complete]3 <= .99, "Yellow")

Combined: 

=IF([% Complete]3<= 0.50), "Red"), IF([% Complete]3<=0.99, "Yellow"), IF([% Complete]3= 1,"Green")))

Any information you can send me about this #unparseable error is greatly appreciated.

Thanks,

Susie

Mike Wilday

Hi Susy, You have a lot of closing parenthesis that are unnecessary. A nested if statement can't close until the end. Try this revised version. I replaced the row number with an @row identifier which will find the value at that row. You can also make this a column formula by right-clicking on it after you put it in a column.

=IF([% Complete]@row<= 0.50, "Red", IF([% Complete]@row<=0.99, "Yellow", IF([% Complete]@row= 1, "Green")))

For more about @row see:

Create Efficient Formulas with @cell and @row | Smartsheet Learning Center

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Optimize your formulas to improve the overall performance of your sheet, and prevent having to manually reference cells in formulas. 

For more about Column Formulas see:

Set Formulas for All Rows with Column Formulas | Smartsheet Learning Center

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

With column formulas, you can apply uniform calculations and expressions to all rows in the sheet.

Mike Wilday

Hi Susy, You have a lot of closing parenthesis that are unnecessary. A nested if statement can't close until the end. Try this revised version. I replaced the row number with an @row identifier which will find the value at that row. You can also make this a column formula by right-clicking on it after you put it in a column.

=IF([% Complete]@row<= 0.50, "Red", IF([% Complete]@row<=0.99, "Yellow", IF([% Complete]@row= 1, "Green")))

For more about @row see:

Create Efficient Formulas with @cell and @row | Smartsheet Learning Center

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Optimize your formulas to improve the overall performance of your sheet, and prevent having to manually reference cells in formulas. 

For more about Column Formulas see:

Set Formulas for All Rows with Column Formulas | Smartsheet Learning Center

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

With column formulas, you can apply uniform calculations and expressions to all rows in the sheet.

Susie Wachowski

Thank you so much Mike!! That was the secret!! 😃