Status Update -- Auto Update the icon with a Rule

tania.cubell81056

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

 

Paul Newcome

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"))))

tania.cubell81056

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

Mike Wilday

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

Paul Newcome

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

tania.cubell81056

• %Complete    
• Finish

 

tania.cubell81056

Attached is a screenshot with column names

 

Wanted to post the result in: Status Icon (column)

 

Mike Wilday

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

Mike Wilday

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

Paul Newcome

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 auto-populate 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.

tania.cubell81056

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.

 

​​​​​​​

Paul Newcome

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?

Mike Wilday

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

tania.cubell81056

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"))))

Mike Wilday

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"))))

 

tania.cubell81056

Hi Mike,

Thanks for responding.  plugged in your formula and  received an "invalid operator" error.

 

Best,

Tania