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

jmo

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

David Joyeuse

Hi again @jmoser

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

Problem in your formula is that when smartsheet reach the second IF statement, everything is below 449, so it is true, and thus it returns "Yellow".

You'll either have to add some AND statement in your IF conditions or arrange your formula differently like;

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

Another way to do it would be:

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

And if you want to stick to what you've already written:

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

Ways to achieve what you want here are legions ;)

Last thing, if you put numbers between " ", they'll be treated as text by smartsheet. Which can creates problem if you want to use those numbers. As a rule, don't put quotes around numbers and they'll be treated as such.

Hope it helped!

David Joyeuse

Hi again @jmoser

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

Problem in your formula is that when smartsheet reach the second IF statement, everything is below 449, so it is true, and thus it returns "Yellow".

You'll either have to add some AND statement in your IF conditions or arrange your formula differently like;

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

Another way to do it would be:

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

And if you want to stick to what you've already written:

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

Ways to achieve what you want here are legions ;)

Last thing, if you put numbers between " ", they'll be treated as text by smartsheet. Which can creates problem if you want to use those numbers. As a rule, don't put quotes around numbers and they'll be treated as such.

Hope it helped!

Paul Newcome

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

@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: