Help w/nested IF statements with numbers that are between certain criteria

Options
jmo
jmo ✭✭✭✭✭✭
edited 08/21/20 in Formulas and Functions

Hi team - I'm trying to create a nested IF statement that shows the following in the Status column:

  • Green status is Total score 450 or above
  • Yellow status is Total score between 449-350
  • Red status is Total score between 349-1
  • Gray status is Total score of 0

The Total score column is a formula that sums up specific columns to the left on the sheet.


The formula I'm experimenting with that doesn't appear to work as needed (although it doesn't error out):


=IF([Total score]@row >= "450", "Green", IF([Total score]@row <= "449", "Yellow", IF([Total score]@row <= "350", "Red", "Gray")))

What am I missing?

Thanks,

Jeff

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You are going to want to remove the quotes from around the numbers. When you put quotes around them, the formula reads it as text and therefor won't register a less than or greater than argument.


    You are also going to want to rearrange your last two IF statements. Nested IFs work from left to right and stop at the first true value. Since 1 is less than 499 and will trigger a true for Yellow, it will stop there and not register the Red. If you put the Red argument first then it should work as intended.

  • jmo
    jmo ✭✭✭✭✭✭
    edited 08/21/20
    Options

    @David Joyeuse - you are my formula savior today!

    I used your solution this way to get at what I needed:

    =IF(AND([Total score]@row >= 350, [Total score]@row < 450), "Yellow", IF([Total score]@row > 450, "Green", IF([Total score]@row <> 0, "Red", "Gray")))


    Had to add a couple of @row entries to your formula and PRESTO - it worked out:


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!