IF, RYG and range

Hi All,

I'm growing absolutely desperate. I have gone through what feels like a milion various posts on here, but nothing quite seems to answer what I need. I am trying to set up RYG conditions based on monthly estimated FTEs so that if the FTE is higher than 1 in any of the columns, the ball goes red.

And I am wondering if anyone can make it simpler? A nice to have would also be to have a condition that if the value in any of the columns in the row is higher than 0.75 to turn it yellow. I'm happy to add helper columns if that is needed.

Now, I have this formula that works, but it appears to be too complicated (please note I have 27 of these columns):

=IF([Oct-20]@row >= 1, "Red", IF([Nov-20]@row >= 1, "Red", IF([Dec-20]@row >= 1, "Red", IF([Jan-21]@row >= 1, "Red", IF([Feb-21]@row >= 1, "Red", IF([Mar-21]@row >= 1, "Red", IF([Apr-21]@row >= 1, "Red", IF([May-21]@row >= 1, "Red", IF([Jun-21]@row >= 1, "Red", IF([Jul-21]@row >= 1, "Red", IF([Aug-21]@row >= 1, "Red", IF([Sep-21]@row >= 1, "Red", IF([Oct-21]@row >= 1, "Red", IF([Nov-21]@row >= 1, "Red", IF([Dec-21]@row >= 1, "Red", IF([Jan-22]@row >= 1, "Red", IF([Feb-22]@row >= 1, "Red", IF([Mar-22]@row >= 1, "Red", IF([Apr-22]@row >= 1, "Red", IF([May-22]@row >= 1, "Red", IF([Jun-22]@row >= 1, "Red", IF([Jul-22]@row >= 1, "Red", IF([Aug-22]@row >= 1, "Red", IF([Sep-22]@row >= 1, "Red", IF([Oct-22]@row >= 1, "Red", IF([Nov-22]@row >= 1, "Red", IF([Dec-22]@row >= 1, "Red", "Green")))))))))))))))))))))))))))


Best Answers


  • Michaela Kamenska
    Michaela Kamenska ✭✭✭✭✭✭
    edited 11/08/20

    Hi Kelly,

    Thank you, that worked!

    Is there a way I could also add another argument to the formula, where anything within the range of 0.75 - 1 turns yellow? I'm just really not good at joining multiple formulas and arguments, so if you can help, that'd be great!

  • Michaela Kamenska
    Michaela Kamenska ✭✭✭✭✭✭

    @David Joyeuse Thank you so much!

    I am so sorry as it may look like a "not very smart" question for many, but I do get errors often when combining multiple arguments and formulas!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭


    Your questions are good questions, and keep them coming! We all learn a lot from each others questions. If you have additional errors in your formulas, post another question. The community is glad to help out.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!