Nested IF/Or/And Formulas

Options

Hi,

I'd like to combine these 2 formulas below but I couldn't. Is anyone please assist me?


My story is:

if column4 and column5 and column6 are bigger than 30, then make column3 green symbol

if one of the column4 or column5 or column6 is between 0 and 30, then make column3 yellow symbol

if one of the column4 or column5 or column6 is <=0 then make column3 red symbol


I've made the formulas below, they are working separately but I couldn't get them combine :

=IF(OR([Column4]@row <= 0; [Column5]@row <= 0; [Column6]@row <= 0); "Red")

=IF(OR([Column4]@row <= 30; [Column5]@row <= 30; [Column6]@row <= 30); "Yellow"; "Green")


Thanks

Tags:

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Burhan,


    Try this in your column 3:

    =IF(AND([Column4]@row > 30; [Column5]@row > 30; [Column6]@row > 30); "Green"; IF(OR([Column4]@row <= 0; [Column5]@row <= 0; [Column6]@row <= 0); "Red"; "Yellow"))

    It translates to:

    If ALL of the following are true (columns 4, 5, and 6 are greater than 30), show green. Otherwise,

    If ANY of the following are true (either column 4, 5, OR 6 is at or below 0), show red. Otherwise,

    Show yellow.


    Hope this helps!

    Best,

    Heather

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Burhan,


    Try this in your column 3:

    =IF(AND([Column4]@row > 30; [Column5]@row > 30; [Column6]@row > 30); "Green"; IF(OR([Column4]@row <= 0; [Column5]@row <= 0; [Column6]@row <= 0); "Red"; "Yellow"))

    It translates to:

    If ALL of the following are true (columns 4, 5, and 6 are greater than 30), show green. Otherwise,

    If ANY of the following are true (either column 4, 5, OR 6 is at or below 0), show red. Otherwise,

    Show yellow.


    Hope this helps!

    Best,

    Heather

  • Yeap, it worked! Thank you Heather 😊

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    Happy to help! Have a great week.

  • but one more question please :)


    why it is not working in these formula below, i'just used or formula first and formula last.


    =IF(OR([Column4]@row <= 0; [Column5]@row <= 0; [Column6]@row <= 0); "Red"; "Yellow");if(and([Column4]@row>30;[Column5]@row>30;[Column6]@row>30);"Green"))


    thanks