Ron and/or Excelent replies on combining and/or

[Guest]

Thanks for the quick response but Im not getting the correct result

{=AND(OR(G59=K59:N59),OR(H59=K59:N59),1,0)}

The value still comes up false yet the result should prove true. G59 is the
first cell.

7431 478641 FALSE

I was given six numbers and then was given 4. I need to represent a 1 if
both of the first two numbers in the four number group ( 7 and 4 ) are found
in the first four numbers ( 4,7,8,6 ) of the six number group ( 478641).

Thanks again!

 

[Ron Rosenfeld]

Thanks for the quick response but Im not getting the correct result

{=AND(OR(G59=K59:N59),OR(H59=K59:N59),1,0)}

Since you've changed more in the formula than the cell references, I'm not
surprised you're not getting correct results.

See previous responses when you were not entering it as an array formula.

The value still comes up false yet the result should prove true. G59 is the
first cell.

7431 478641 FALSE

I was given six numbers and then was given 4. I need to represent a 1 if
both of the first two numbers in the four number group ( 7 and 4 ) are found
in the first four numbers ( 4,7,8,6 ) of the six number group ( 478641).

Thanks again!

--ron

 

[Guest]

It works with 2 or 3 OR statements but when I try 4 it results in FALSE. Do
you see anything wrong with it?

g h i j k l m n p

59 7 4 3 1 7 4 3 1 FALSE

{=AND(OR(G59=K59:N59),OR(H59=K59:N59),OR(I59=K59:N59),OR(J59=K59:N59))}

 

[Guest]

Just as a note: the braces were there, they do not copy and paste. The 1,0
addition was advice to how to make the cell turn to a 1. I just
conditionally set it to turn red instead.

 

[Ron Rosenfeld]

It works with 2 or 3 OR statements but when I try 4 it results in FALSE. Do
you see anything wrong with it?

g h i j k l m n p

59 7 4 3 1 7 4 3 1 FALSE

=AND(OR(G59=K59:N59),OR(H59=K59:N59),OR(I59=K59:N59),OR(J59=K59:N59))

I pasted your data into G59:N59

I copied your formula and pasted it into a cell, entering with <ctrl><shift>

I got a result of TRUE.

As I wrote before, the most likely problem is with your data. One or more of
your values are TEXT and not NUMBERS.

Check them with ISTEXT.

In G60 enter the formula: =ISTEXT(G59)

Then copy/drag across to N60 and see if any return TRUE.


--ron

 

[Ron Rosenfeld]

Just as a note: the braces were there, they do not copy and paste. The 1,0
addition was advice to how to make the cell turn to a 1. I just
conditionally set it to turn red instead.

The 1,0 addition cannot possibly do that. =AND(1,0) will always return FALSE,
no matter how many other conditions are present.


--ron

 

[Guest]

xxxx-xxxx
{=IF(OR(G59=K59:N59,H59=K59:N59),1,0)}
{=IF(OR(--G59=--K59:N59,--H59=--K59:N59),1,0)} * use this if any textvalues

xxxx-xxxxxx
{=IF(OR(G59=K5959,H59=K5959),1,0)}
{=IF(OR(--G59=--K5959,--H59=--K5959),1,0)} * use this if any textvalues



"Shu of AZ" skrev:

 

[Guest]

Incorrect result for both types of formulas- all are numbers and not text.
Do you see anything wrong. Ron's results in the wrong answer if the numbers
are moved around ( see bottom example ).
Testing of the formulas by rearranging the numbers proved incorrect on both
formulas.
The oddest is that as you change numbers in the four number set, the result
will remain the same, give you an incorrect result, or change with only one
number being added that is in the entire six set.

It seems to work on the intial test but as you are changing the four set,
things start happening that result in an incorrect result.

(Excelent formula) Gave a 1 when the four set was 4,5,6,7 but remained a 1 when the new set came in as 1,2,7,4

g h i j k l m n o p q

60 1 2 7 4 4 5 6 7 8 2 1

q60 content - {=IF(OR(G60=K6060,H60=K6060),1,0)}

(Ron's formula) Gave a TRUE at first with 7,4,2,1 but as the numbers came in rearranged in the four set, it went to false.

g h i j k l m n o p q

59 1 2 4 7 7 4 2 1 8 2 FALSE

q59 content - {=AND(OR(G59=K59:N59),OR(H59=K59:N59),OR(I59=K59:N59),OR(J59=K59:N59))}