Nested IF Statement Formula

Hello Team,

This would be a great formula to incorporate into my daily SmartSheets process within my organization. I would like to know how to create a nested IF statement if this is the correct formula to use, that uses an RYG (red, yellow, green) light flag formula to set the status of a task based on its percent complete. I have not been able to figure this one out. If a task is less than 50% complete, turn the RYG light "Red" If a task is between 50-99% complete, turn the RYG light "Yellow" If a task is 100% complete, turn the RYG light "Green"

Thank you,

Find more posts tagged with

Education

Formulas

IT & Operations

Project Management

Comments

Mike Wilday

Try this formula. Replace "Percentage complete column name" with your own column name. And replace 23 througout the formula with whatever row you are referencing.

=IF([Percentage complete column name]23 < .5, "Red", IF(AND([Percentage complete column name]23 >= .5, [Percentage complete column name]23 < 1), IF([Percentage complete column name]23 = 1, "Green")))

Todd Harrison

Mike, I also had the same question and tried your formula with no luck. I changed the % Completed to reference the appropriate cell but kept getting an "Unparsable" error.

Chris.fowler49286

Hello Mike,

What row and column should the formula be inserted to?

Thanks Mike

IF Statement Test 2018.PNG

Mike Wilday

Whoops I forgot the then response for the yellow section. Try this rewrite. Make sure to update the column name and the row number.

=IF([Percentage complete column name]23 < .5, "Red", IF(AND([Percentage complete column name]23 >= .5, [Percentage complete column name]23 < 1), "Yellow", IF([Percentage complete column name]23 = 1, "Green")))

Chris.fowler49286

Hello Mike,

I cant get it to work, also which row and column should the formula be added.

Thanks,

Chris.fowler49286

Hello Mike,

Got it and it works!

Thank you Sir,

Mike Wilday

You are welcome! Glad I could be of help!

Todd Harrison

That worked for me as well. Thanks for the help and quick response.

Chris McKay

I'm amazed that this is not built into Smartsheet. If it's not included in the Project Settings as a configuration item, at least create a template for these poor users. Seriously.... what other question is asked more on these Community forums other than "Automate RYG"?

Mike Wilday

I agree. The issue of percentages displaying as decimals is very hidden and not intuitive. If not for greats like J. Craig Willams I would not have know this information.

carsonc

Was able to tailor this to my sheet as well - thank you!

=IF([% Complete]23 < .5, "Red", IF(AND([% Complete]23 >= .5, [% Complete]23 < 1), "Yellow", IF([% Complete]23 = 1, "Green")))

Please note that the #s next to [% Complete] reflect the row #.

Sign in to join the conversation!

Quick Links

Trending discussions

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…