IF formula help to gray out a status

Hi Community...having issues with this formula working for the "Gray"Status. The other criteria is working so can't figure out why I can't get gray status to work.

=IF([Actual Progress]10 >= ($[Week 1 Target Progress (Aug 22)]10 * 0.8), "Green", IF(AND([Actual Progress]10 < ($[Week 1 Target Progress (Aug 22)]10 * 0.8), [Actual Progress]10 >= ($[Week 1 Target Progress (Aug 22)]10 * 0.6)), "Yellow", IF(AND([Actual Progress]10 < ($[Week 1 Target Progress (Aug 22)]10 * 0.6), [Actual Progress]10 > 0, "Red", IF([Actual Progress]10 = 0, "Gray")))))

Screen Shot 2019-08-16 at 1.54.38 PM.png

Find more posts tagged with

Formulas

Conditional Formatting

Comments

Paul Newcome

Your final AND is not closed. Try closing that and see if that helps.

Eric M Oliveira

Hello

That is correct, as Paul mentioned above there appears to be a missing AND function, close parenthesis ), just before the value "Red". The below formula contains this correction.

Also, may want to try switching Gray to the first function. Depending on what values the formula produces it may be that order of operations isn't allowing Gray to appear. This would appear like this:

=IF([Actual Progress]10 = 0, "Gray", IF([Actual Progress]10 >= ($[Week 1 Target Progress (Aug 22)]10 * 0.8), "Green", IF(AND([Actual Progress]10 < ($[Week 1 Target Progress (Aug 22)]10 * 0.8), [Actual Progress]10 >= ($[Week 1 Target Progress (Aug 22)]10 * 0.6)), "Yellow", IF(AND([Actual Progress]10 < ($[Week 1 Target Progress (Aug 22)]10 * 0.6), [Actual Progress]10 > 0), "Red")))))

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

Paul Newcome

Eric,

I didn't really look at it this way until I read your post. The way the formula reads, all of the previous criteria sets have to be above 0 to be true which means the placement of the "Gray" shouldn't be an issue.

However... It got me to REALLY looking at the formula, and (correct me if I am wrong) I feel like we should be able to rewrite this without any AND statements. If you look at each AND, it is saying "if this is true and the previous criteria is false". If the formula has already progressed past the previous criteria, then by default it must be false.

This should work the same:

=IF([Actual Progress]@row >= $[Week 1 Target Progress (Aug 22)]@row * 0.8, "Green", IF([Actual Progress]@row>= $[Week 1 Target Progress (Aug 22)]@row* 0.6, "Yellow", IF([Actual Progress]@row> 0, "Red", "Gray")))

Sign in to join the conversation!

Quick Links

Trending discussions

Help With If Count If to Identify Unique Values in a Column
I'm trying to make a helper column to identify if a new/unique project code has been entered in a column so that I know when a new project has been created. I've been trying to set up this formula and plan to then set up an automation to be notified when "New Project Code" has been entered into a cell. It seems to be ok on…
Logging separate data points of the same type of data for one row
Looking for a way to easily log and reference multiple data points for the same type of data (ex: successful phone contacts) that all show for one entry (by member name). Want this to be both easy to enter the information (ideally by interacting with the same row via Dynamic View), and easy to see data on the back end of…
Why does the parent task my % column show as a decimal value instead of a percentage?
Why does the parent task my % column show as a decimal value instead of a percentage? I checked the column and project settings, and they are identical to another smartsheet in which the values show up as a percentage.