Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Nested IF statement for RYGB only works in one row

edited 12/09/19 in Archived 2017 Posts

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.

Row 46.png

Row 47.png


  • Mike Wilday
    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
    J. Craig Williams ✭✭✭✭✭✭


    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.



  • 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!

This discussion has been closed.