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

 

[shg]

This formula isn't doing what you think:

=OR(A1,B1={1})

It will return TRUE if there is any non-zero number (or the value TRUE) in A1.

You can use these:

=OR(A1:B1=1)
=OR(A1:B1="No")

... but both MUST be confirmed with Ctrl+Shift+Enter.

 

[AMAS]

Thanks. I guess I need to revist the textbooks (and webpages) to understand what the array does exactly.

This works for the example I gave, but I have a quick question. How do I do the same thing with a non-continuous range (e.g. A1, C1)?

I tried the same concenpt but it doesn't seem to work (confirmed by Ctrl+Alt+Enter):

OR(A1,C1="No")

AMAS

 

[Aladin Akyurek]

Thanks. I guess I need to revist the textbooks (and webpages) to understand what the array does exactly.

This works for the example I gave, but I have a quick question. How do I do the same thing with a non-continuous range (e.g. A1, C1)?

I tried the same concenpt but it doesn't seem to work (confirmed by Ctrl+Alt+Enter):

OR(A1,C1="No")

AMAS

Control+shift+enter (CSE), not just enter:

=OR(CHOOSE({1,2},A1,C1)="no")

A bit more generic, also requiring CSE:

=OR(CHOOSE(ROW(INDIRECT("1:"&AREAS((A1,C1)))),A1,C1)="No")

though expensive.

 

[AMAS]

Thanks Aladin. I have been reading about non-contiguous ranges and it seems that when the developers werer building the array function, they never thought much about devising ways for non-contiguous arrays to be as simple as contiguous arrays. You would think that this would be a no-brainer, but its obviously not.

I will test your solutions and post back.

AMAS

 

[AMAS]

Both work perfectly. I think I will go with the first approach since I can remember it better for future use, unless there is an inherent advantage in using the second approach.

Thanks again.

AMAS

 

[T. Valko]

Thanks. I guess I need to revist the textbooks (and webpages) to understand what the array does exactly.

This works for the example I gave, but I have a quick question. How do I do the same thing with a non-continuous range (e.g. A1, C1)?

I tried the same concenpt but it doesn't seem to work (confirmed by Ctrl+Alt+Enter):

OR(A1,C1="No")

AMAS

Like this...

=OR(A1="No",C1="No")

 

[Aladin Akyurek]

Both work perfectly. I think I will go with the first approach since I can remember it better for future use, unless there is an inherent advantage in using the second approach.

Thanks again.

AMAS

The second approach would be useful if you have many non-contiguous cells. By the way, you can also simply test them for equality one by one within OR...

=OR(A1="no",C1="no",E4="no")

which is cheaper.

 

[AMAS]

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

 

[AMAS]

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

AMAS