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

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…