What's wrong with my RYGB formula?

twaiteORELD

Ok, I'm feeling really stumped.  I have this formula for changing the color of the RYGB balls based on the number in a specific cell but it is returning an #INVALIDOPERATION on anything except zero (when the ball turns blue). Where am I messing this up?

=IF([# of Days from Submission to Assignment]@row = 0, "Blue", IF([# of Days from Submission to Assignment]@row > 0 < 5, "Green", IF([# of Days from Submission to Assignment]@row > 5 < 7, "Yellow", IF([# of Days from Submission to Assignment]@row > 7, "Red"))))

Thanks All!  Teresa

Mike Wilday

You can't use to operators like >0 <5. You could use an AND statement but based on your formula you don't need to specify the smaller amount... IF the item is not 0 then it will look at the green requirements which is less than 5. Then if its more than 5 it will look at the yellow requirements which is less than or equal to seven. I added the = there because your current statement wasn't accounting for the number 7. Then if its not less than or equal to seven it will move to the final stage which is anything greater than 7. 

Try this out. 

=IF([# of Days from Submission to Assignment]@row = 0, "Blue", IF([# of Days from Submission to Assignment]@row< 5, "Green", IF([# of Days from Submission to Assignment]@row<= 7, "Yellow", IF([# of Days from Submission to Assignment]@row > 7, "Red"))))

twaiteORELD

That's IT!  I knew there was something about the details I was putting in, but I couldn't put my finger on it.  You just helped my get through a massive brain block - Thank you!

Mike Wilday

You're welcome! 

You can also use those parameters by adding and AND statement... like this... 

=IF([# of Days from Submission to Assignment]@row = 0, "Blue", IF(AND([# of Days from Submission to Assignment]@row< 5, [# of Days from Submission to Assignment]@row> 0), "Green"... 

But in your case its not necessary. 

https://help.smartsheet.com/function/and

Paul Newcome

Using the additional IF statement for the Red criteria is also not needed. Not to say it is incorrect, but something to keep in mind if you end up doing more complex or longer nested IF's. You can utilize the "else" section of the IF statement to designate the final option.

 

=IF([# of Days from Submission to Assignment]@row = 0, "Blue", IF([# of Days from Submission to Assignment]@row< 5, "Green", IF([# of Days from Submission to Assignment]@row<= 7, "Yellow", "Red")))

.

Another thing that can be done is to work backwards to ensure your priority is looked at FIRST.

=IF([# of Days from Submission to Assignment]@row > 7, "Red", IF([# of Days from Submission to Assignment]@row > 5, "Yellow", IF([# of Days from Submission to Assignment]@row > 0, "Green", "Blue")))

.

In this particular case it can work either way, but again... With the longer and/or more complex nested IF's, you can prioritize your conditions to help avoid using additional statements such as AND' and OR's.

Mike Wilday

Hi Paul, All good points. but my only concern assuming the red, is if there is ever a negative number result then those would be red too. And that might not be an intended response.

Paul Newcome

That is a concern with the first formula. That's why I included writing it backwards. Any negative numbers would show as Blue although judging by the column name, it seems as though there is not the possibility of a negative number.

Mike Wilday

Yeah, I agree.