Array and text

[AMAS]

I can't figure this out and any help is appreciated. If I put a number (e.g. 1) in A1 and another number in B1 (e.g. 2) and use this formula:

=OR(A1=1,B1=2)

Or this formula:

=OR(A1,B1={1})

then I get the correct answer: TRUE

Now if I use words. For example:

A1 = Yes
B1 = No

And use the formula:

=OR(A1="Yes",B1="No")

I get the correct answer: TRUE

If I try to use an array, it works only if I ask if the word "No" is present, but not "Yes".

=OR(A1,B1={"No"})

but this one doesn't work:

=OR(A1,B1={"Yes"})

Why is this inconsistency happening and are there any rules I should be aware of regarding this issue?

Thanks.

AMAS

 

[T. Valko]

Thanks. My actual arrary uses six non-contiguous ranges (each one cell) that has three possible text statements (yes, no, unclear). The problem is that in the end, you have to give an overall assesment. We use a simple formula: if any of the answers were 'no', then the overall is 'no'. To get an overall 'yes', you would have to get a 'yes' on all the six items, else you get an 'unclear'.

My original formula without the array was:

=IF(OR(D2="NO",F2="NO",H2="NO",J2="NO",L2="NO",N2="NO"),"NO",IF(OR(D2="UNCLEAR",F2="UNCLEAR",H2="UNCLEAR",J2="UNCLEAR",L2="UNCLEAR",N2="UNCLEAR"),"UNCLEAR","YES"))

Now I have modified it to look like this:

=IF(OR(CHOOSE({1,2,3,4,5,6},D2,F2,H2,J2,L2,N2)="no"),"NO",IF(OR(CHOOSE({1,2,3,4,5,6},D2,F2,H2,J2,L2,N2)="UNCLEAR"),"UNCLEAR","YES"))

Thanks everyone for your advice and help on this.

AMAS

Just a thought...

What's in the cells between the target cells?

If none of them contain NO or Unclear then you can use a much simpler formula.

=IF(COUNTIF(D2:N2,"No"),"No",IF(COUNTIF(D2:N2,"Unclear"),"Unclear","Yes"))

 

[Aladin Akyurek]

Aladin, do you mean expensive and cheap regarding the amount of CPU and memory?

AMAS

Processing time that CPU (Excel) needs...

By the way, if those 6 cells can be guaranteed to always 6 values consisting YES, NO, and UNCLEAR values...

=LOOKUP(9.99999999999999E+307,MATCH({"YES";"UNCLEAR";"NO"},CHOOSE({1,2,3,4,5,6},D2,F2,H2,J2,L2,N2),0),{"YES";"UNCLEAR";"NO"})

might be of interest to you. This should be similar to

=IF(OR(CHOOSE({1,2,3,4,5,6},D2,F2,H2,J2,L2,N2)="no"),"NO",IF(OR(CHOOSE({1,2,3,4,5,6},D2,F2,H2,J2,L2,N2)="UNCLEAR"),"UNCLEAR","YES"))

you posted.

Note. You can shorten 9.99999999999999E+307 to just 9.99E+307.

 

[T. Valko]

Processing time that CPU (Excel) needs...

By the way, if those 6 cells can be guaranteed to always 6 values consisting YES, NO, and UNCLEAR values...

=LOOKUP(9.99999999999999E+307,MATCH({"YES";"UNCLEAR";"NO"},CHOOSE({1,2,3,4,5,6},D2,F2,H2,J2,L2,N2),0),{"YES";"UNCLEAR";"NO"})

Note. You can shorten 9.99999999999999E+307 to just 9.99E+307.

That can be simplified to:

=LOOKUP(7,MATCH({"YES";"UNCLEAR";"NO"},CHOOSE({1,2,3,4,5,6},D2,F2,H2,J2,L2,N2),0),{"YES";"UNCLEAR";"NO"})

 

[AMAS]

Thank you both for these great suggestions. I can confirm that the cells between the cells that I am pulling in the cells do not contain 'no', 'yes' or 'unclear'. They are descriptions of why the choice was made for each item.

I am new to arrays, and like a kid with a new toy, I was blinded by the shiny colors withouth thinking about practicality.

Thanks for showing me multiple ways to accomplish this task.

AMAS

 

[T. Valko]

Thank you both for these great suggestions. I can confirm that the cells between the cells that I am pulling in the cells do not contain 'no', 'yes' or 'unclear'. They are descriptions of why the choice was made for each item.

I am new to arrays, and like a kid with a new toy, I was blinded by the shiny colors withouth thinking about practicality.

Thanks for showing me multiple ways to accomplish this task.

AMAS

Sometimes we get distracted by all the obfuscation!

Thanks for the feedback!