Function IF AND OR (main Value cell) nested to multiples cells to get exact Result

[AndyJR]

Hi,

I trying to display depending on values from Main cell (cell C) nested with Multiples cell, so far a i have good result but i still need to add a criteria on more cells with some values.
i made a short formula that is working :

=IF(AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="1",E4="0"),"Stay","AWAY")

But to existing formula i need to add multiples different values of Cell D and E to get a correct display :

Example : If
C4 have One of these Value-> 000, 001, 002, 010, 011
and
D4 have --> 1
and
E4 have --> 1
and
F4 have --> 3
then Display -> "Stay" if FALSE then "AWAY"

Then at same Formula i need to add last part of the formula for Cell D4, E4 and F4 with different values, example
Cell (D4 have --> 2, E4 have --> 1, F4 have --> 2 )
Within the same formula but with out adding the Long formula applied to C4
(can i do that?)

Thank you very much for any help (or link suggestion)

 

[etaf]

try

=OR(AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="1",E4="0"),AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="1",E4="1",F4="3"),AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="2",E4="1",F4="2"))

NOT that "000" is a text value and not a number 0
where you have put "" the formula is looking for text and not numbers

 

[AndyJR]

Hi etaf,
Wow, thanks i appreciated a lot
after work i'll test it..

Thank you !!!

 

[AndyJR]

Hi etaf

Just asking, On your formula where exact i write the text "Stay", "away" ("Stay" if TRUE or "away" if FALSE) ?

=OR(AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="1",E4="0"),AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="1",E4="1",F4="3"),AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="2",E4="1",F4="2"))

Thanks

 

[snjpverma]

Andy,
If etaf's formula is giving you the exact result then you can embed it in IF function like this

=IF(OR(AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="1",E4="0"),AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="1",E4="1",F4="3"),AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="2",E4="1",F4="2")),"Stay","Away")

 

[etaf]

in your original formula


=IF(AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="1",E4="0"),"Stay","AWAY")

you are replacing the AND(............) in your formula with the OR in mine

so
in your original formula


=IF(OR(AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="1",E4="0"),AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="1",E4="1",F4="3"),AND(OR(C4="000",C4="001",C4="002",C4="010",C4="011"),D4="2",E4="1",F4="2")),"Stay","AWAY")

but "2" is not the same as 2
so "2" is text 2
2 is number

 

[snjpverma]

Andy,
do the cells contain numbers or text? why have you written the numbers in column D, E and F in those inverted commas?

if it is in text format then it's okay otherwise you can write it directly

 

[AndyJR]

Hi etaf.

I'll won't forget the Info, "2" is Text , 2 is number.

Thank you!

 

[AndyJR]

Hi snjpverma,

Yes, the column D,E and F are numbers, i'll write it direct.

Thank you so much!!

 

[snjpverma]

I am not sure, but see if this formula returns the same result?

=IF(AND(OR(C4={"000","001","002","010","011"}),OR(D4={1,2}),OR(E4={0,1}),OR(F4={2,3})),"Stay","AWAY")