nested If

[RUDYTX]

Hello!
I need help with this formula. Right now this formula is true if all the values in red are E, 1, N or all the values in blue are E, 1, N
Sometimes my data base wont have data in red or blue, and sometimes will have both. When it has data in red and blue i want my formula to consider the data in blue and ignore the data in red to be true.

Thanks in advance

	A	B	C	D	E	F	G	H	I	J	K
1
2
3	10	STD	4	4	E	1	N	E	1	N	YES
4

<tbody>
</tbody>

K3:
=IF((AND(A3=10,OR(B3="STD",AND(C3=4,D3=4)),OR(AND(E3="E",F3=1,G3="N"),AND(H3="E",I3=1,J3="N")))),"YES","NO")

 

[etaf]

,OR(AND(E3="E",F3=1,G3="N"),AND(H3="E",I3=1,J3="N")))

so that is using RED OR BLUE

Does just that formula work for you

 

[RUDYTX]

I does work for blue or red. Sometimes I want my formula to use only blue (instead of blue or red) to be true.

 

[etaf]

Dont understand , IF BLUE fits the criteria you get a TRUE
What conditions would you only USE Blue?

can you give an example

 

[RUDYTX]

Sorry, maybe I didn't explain correctly. This is the scenario:
If red is E 1 N and blue is E 0 N I need to get a False

Thanks for your help

 

[yky]

=if((and(a3=10,or(b3="std",and(c3=4,d3=4)),and(and(e3="e",f3=1,g3="n"),and(h3="e",i3=1,j3="n")))),"yes","no")

 

[RUDYTX]

Thanks for your help. I tried it but also works for two scenarios. It needs to work for all these. Thanks again

E 0 N E 1 N TRUE
E 1 N E 0 N FALSE (it works)
E 0 N E 0 N FALSE
E 1 N E 1 N TRUE (it works)
E 1 N (NO DATA) TRUE
(NO DATA) E 1 N TRUE

this formula =IF((AND(A3=10,OR(B3="STD",AND(C3=4,D3=4)),OR(AND(E3="E",F3=1,G3="N"),AND(H3="E",I3=1,J3="N")))),"YES","NO")
works for all these except for E 1 N E 0 N FALSE

 

[yky]

Thanks for your help. I tried it but also works for two scenarios. It needs to work for all these. Thanks again

E 0 N E 1 N TRUE
E 1 N E 0 N FALSE (it works)
E 0 N E 0 N FALSE
E 1 N E 1 N TRUE (it works)
E 1 N (NO DATA) TRUE
(NO DATA) E 1 N TRUE

this formula =IF((AND(A3=10,OR(B3="STD",AND(C3=4,D3=4)),OR(AND(E3="E",F3=1,G3="N"),AND(H3="E",I3=1,J3="N")))),"YES","NO")
works for all these except for E 1 N E 0 N FALSE

Are those the only scenarios we need to consider or there are others? If there are others, please list them all.

 

[yky]

Thanks for your help. I tried it but also works for two scenarios. It needs to work for all these. Thanks again

E 0 N E 1 N TRUE
E 1 N E 0 N FALSE (it works)
E 0 N E 0 N FALSE
E 1 N E 1 N TRUE (it works)
E 1 N (NO DATA) TRUE
(NO DATA) E 1 N TRUE

this formula =IF((AND(A3=10,OR(B3="STD",AND(C3=4,D3=4)),OR(AND(E3="E",F3=1,G3="N"),AND(H3="E",I3=1,J3="N")))),"YES","NO")
works for all these except for E 1 N E 0 N FALSE

Judging from the examples you gave, as long as blue is E 1 N, the result must be true. So, you may want to start with

=IF(AND(H3="E",I3=1,J3="N"),"YES",IF(blue has no data,"YES",if(red is E 1 N,"YES,"no more scenario")))

 

[jasonb75]

Try this change to your original formula

=IF((AND(A3=10,OR(B3="STD",AND(C3=4,D3=4)),OR(AND(COUNTA(H3:J3)=0,E3="E",F3=1,G3="N"),AND(H3="E",I3=1,J3="N")))),"YES","NO")

I've assumed that for 'no data', that all 3 blue cells should be empty.