Nested IF statement for RYGB only works in one row

thia.davis

Has anyone come across a scenario where your nested IF statements work perfectly on one row, but when dragged to other rows don't apply the conditions correctly?

 

I've added the following statement to row 47 of my sheet:

=IF([Percentage Complete]47 = 100, "Blue", IF([Percentage Complete]47 < 33, "Red", IF([Percentage Complete]47 < 66, "Yellow", "Green")))

It works just fine in the RYGB Status column. My Percentage Complete column is driven by the project settings.

However, when I add it to row 46, no matter the value I add to Percentage Complete, the ball is always red.

Note how the Percentage Complete in Row 47 does not automatically add in the % sign, but Row 46 does. This is not my doing, and I cannot format the column to not add in the % sign...I have no clue as to why the % sign does not populate for Row 46

Any insight will do, and please see my screen shots.

Mike Wilday

Try making the 100 a 1 and see if that affects it. Is the percentage column a dropdown column? Can you click on it and select 100% from a list? 

J. Craig Williams

thia,

One can tell from the screen shots that this is a formatting issue as Mike implies.

Row 47 shows "100" while Row 46 shows "100%"

100 could be either text or more like a number, with a value of 100 based on your successful formula on row 47.

100% is either text (very unlikely) or a number formatting for percentage. A value of 1 in a cell with % formatting will display 100%.

Hope that helps.

Craig

 

thia.davis

No, it's not a dropdown column, but I will see what happens when I enter a 1. 

thia.davis

Thanks, Craig. The entire Percentage Complete column is determined by SS's Project Mangement setting. I'm not sure how I can change the formatting. In other words, each row in that column should be formatted the same. I'll try to update the formatting anyway.

I appreciate the feedback!