What's wrong with my RYGB formula?

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

Find more posts tagged with

Sheets

Finance

Education

Formulas

Government

Project Management

Comments

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.

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…