Any cell below the stock Level, RYG become to red.

Options
richard_uy
richard_uy ✭✭
edited 12/09/19 in Smartsheet Basics

Hi,

As per the attached and formula, How can i add the formula if Week 19,20,21~52 any column below the stock Level, RYG to red.

I'm only know the formula for single cell. 

=IF(ISBLANK([Stock Level]3), "Yellow", IF([Stock Level]3 <= [Week 19]3, "Green", "Red"))

Thanks in advance.

Screen Shot 2018-04-11 at 12.33.18 PM.png

Comments

  • Shaine Greenwood
    Shaine Greenwood Employee
    edited 04/16/18
    Options

    Hi Richard,

    Use the AND function in your formula:

    =IF(ISBLANK([Stock Level]3), "Yellow", IF(AND([Stock Level]3 <= [Week 19]3, [Stock Level]3 <= [Week 20]3, [Stock Level]3 <= [Week 21]3),"Green", "Red"))

    The above formula returns green as long as all three are equal to or above 5000. If any of them fall below 5000, it should return red. More on AND in the help center: https://help.smartsheet.com/function/and

    Here's our complete function list: https://help.smartsheet.com/functions

  • richard_uy
    Options

    Hi Shaine,

    It won't work, is there any formula missing?

  • Shaine Greenwood
    Options

    Hi Richard,

    Can you give me more information? What's not working, specifically? Are any errors not being returned? Is it just not returning what you expect it to?

  • richard_uy
    Options

    Hi Shaine,

    There is no responds when i input the qty into week 19 & 20. the Formura will work only when i type the content into Week 21. 

    =IF(ISBLANK([Stock Level]3), "Yellow", IF(AND([Stock Level]3 <= [Week 19]3, [Stock Level]3 <= [Week 20]3, [Stock Level]3 <= [Week 21]3), "Green", "Red"))

  • Shaine Greenwood
    Options

    Hi Richard,

    That's due to the AND function, which requires that all conditions be met before setting the status to Green, otherwise it will return Red.

    If you're looking for a function that returns Green when at least one condition is met, use OR in place of AND. I think that you'd also want to have the formula return Red if they fall below the stock level. Try this:

    =IF(ISBLANK([Stock Level]3), "Yellow", IF(OR([Stock Level]3 > [Week 19]3, [Stock Level]3 > [Week 20]3, [Stock Level]3 > [Week 21]3), "Red", "Green"))