Status Update  Auto Update the icon with a Rule
hope you may assist as I've seen you post about other Status formulas.
I'm trying to write the "IF" statement to look at status update the Icon to Red, Yellow, Green or Blue
=IF((AND([% Complete]@row <> 1, TODAY()  Finish@row > 1), "Red"), IF(AND ([% Complete]@row <= .75, TODAY()  Finish@row =< 5), "Yellow"), IF(AND [% Complete]@row > .75, TODAY()  Finish@row < 5), "Green" ), IF ( [% Complete]@row = 1, "Blue") )
I'm experiencing a Syntax error
Here's what i want it to do:
1. If %Complete <100%, and > 1 days AFTER Finish = RED
2. If % Complete <= 75%, and <= 5 days BEFORE FINISH = YELLOW
3. If % Complete >75% , and >5 days BEFORE Finish = GREEN
4. If % Complete = 100% = BLUE
Comments

You have some extra parenthesis in there, some missing parenthesis, and some extra spaces. Try this and see if that gets rid of the error...
=IF(AND([% Complete]@row <> 1, TODAY()  Finish@row > 1), "Red", IF(AND ([% Complete]@row <= .75, TODAY()  Finish@row =< 5), "Yellow", IF(AND([% Complete]@row > .75, TODAY()  Finish@row < 5), "Green", IF([% Complete]@row = 1, "Blue"))))

Hi Paul,
Thank you for the recommendation  updated it a bit to close the space in the Column Title "%Complete"
Here's the revised formula (minus that space):
=IF(AND([%Complete]@row <> 1, TODAY()  Finish@row > 1), "Red", IF(AND ([%Complete]@row <= .75, TODAY()  Finish@row =< 5), "Yellow", IF(AND([%Complete]@row > .75, TODAY()  Finish@row < 5), "Green", IF([%Complete]@row = 1, "Blue"))))
Can't figure out where i'm going wrong  as still throws an "Unparseable" error.
Appreciate any ideas you may have and again many thanks for responding

Keep the space in the % complete as that is probably the actual column title.

Your commas, parenthesis, etc. all seem to be in the right places. What are the EXACT column names in the sheet?

 %Complete
 Finish

Attached is a screenshot with column names
Wanted to post the result in: Status Icon (column)

Spread out your column for %complete. You might find a space in there.

spread out = make it wider so the title is on one line.

When I referenced extra spaces in my original reply, I was referring to extra spaces between parenthesis and whatnot. Not column names. Things like... ) ) instead of )) and IF( [ instead of IF([......
If there wasn't a space between the % and Complete, it would show as "%Compl....." in the column header.
Because it is able to shift the column name into two separate lines, that means there is a space.
To help with this...
When writing your formula, click on a cell (any cell will do) within that column to autopopulate the cell reference [Column Name]#.
You can then delete the number and type in the correct row number or type in @row (whichever is needed). That will ensure your column name is definitely correct within the formula.
I use this a lot when I am doing longer formulas with @row ranges where the text bar hides the row I am on.

checked the column names with your suggestion  and it is %Complete  implemented the suggestion to click on the cell to populate the formula. Still running into the Unparseable error.

Can you post a published link to the sheet with dummy data in it so we can actually get in there and take a look around to see if its maybe something we aren't thinking of?

Try removing the brackets off of the %Complete title. Since there is no space, there is no need for the brackets.
So it would read %Complete@row

Here's the formula trying to use:
IF(AND(%Complete@row < 1, Finish@row TODAY() > 1), "Red", IF(AND (%Complete@row <= .75, TODAY()  Finish@row =< 5), "Yellow", IF(AND(%Complete@row > .75, TODAY()  Finish@row < 5), "Green", IF(%Complete@row = 1, "Blue"))))

There was a space after one of the ANDs... does this one work?
=IF(AND(%Complete@row < 1, Finish@row TODAY() > 1), "Red", IF(AND(%Complete@row <= .75, TODAY()  Finish@row =< 5), "Yellow", IF(AND(%Complete@row > .75, TODAY()  Finish@row < 5), "Green", IF(%Complete@row = 1, "Blue"))))

Hi Mike,
Thanks for responding. plugged in your formula and received an "invalid operator" error.
Best,
Tania
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 202 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!