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

richard_uy

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.

Shaine Greenwood

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

Hi Shaine,

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

Shaine Greenwood

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

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

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"))