Combining formulas, "and" & "or" to verify content of multiple cel

[Guest]

I have four cells with numbers in each, (2)(4)(9)(1), then I have 6 adjacent
cells with numbers in each of them, (4)(3)(5)(8)(9)(2).
In a different cell, I need to represent a true statement that indicates if
the first two numbers in the cell's content, (2) or (4), are contained in any
of the 6 cell's content,(4)(3)(5)(8)(9)(2). If there is a match, in the one
cell that has to represent a hit, it will place a 1 in the cell. Then with a
conditional formula I can turn the cell red.

=if(or(a1=k1,a1=k2,a1=k3,a1=k4,a1=k5,a1=k6, ???(AND(a2=k1 and so on, , ,

How do you combine the OR and AND statement in one formula or is there
another way to do this.

 

[Guest]

=IF(OR(A1=K1:K6,A2=K1:K6),1,0) hit CTRL+SHIFT+ENTER



"Shu of AZ" skrev:

 

[Ron Rosenfeld]

On Sat, 14 Oct 2006 11:41:01 -0700, Shu of AZ <Shu of

I have four cells with numbers in each, (2)(4)(9)(1), then I have 6 adjacent
cells with numbers in each of them, (4)(3)(5)(8)(9)(2).
In a different cell, I need to represent a true statement that indicates if
the first two numbers in the cell's content, (2) or (4), are contained in any
of the 6 cell's content,(4)(3)(5)(8)(9)(2). If there is a match, in the one
cell that has to represent a hit, it will place a 1 in the cell. Then with a
conditional formula I can turn the cell red.

=if(or(a1=k1,a1=k2,a1=k3,a1=k4,a1=k5,a1=k6, ???(AND(a2=k1 and so on, , ,

How do you combine the OR and AND statement in one formula or is there
another way to do this.

Assuming the 4 numbers are in A1:A4, and the 6 numbers are in B1:B6, it sounds
as if you want to return a 1 if A1 is found in B1:B6 and also A2 is found in
B1:B6.

There are many ways of doing that.

One way is with an **array** formula:

=AND(OR(A1=B1:B6),OR(A2=B1:B6))

To enter an **array** formula, after typing in the formula, hold down
<ctrl><shift> while hitting <enter>. XL will place braces {...} around the
formula.

This will return either TRUE, if both match, or FALSE if only one or none
matches.

You should be able to use the formula either to conditionally format, or if you
must have a one or zero, then precede it with a double unary:

=--AND(OR(A1=B1:B6),OR(A2=B1:B6))


--ron

 

[Guest]

Ron, thanks for helping with this formula but I still am not able to produce
the correct answer as seen in AB.

Cell AB59 has this formula in it =AND(OR(G59=K59:N59),OR(H59=K59:N59),1,0)

The logic is this. If G59 has a value found in one of the 4 cells K59:N59
and H59 has a value found in one of the 4 cells K59:N59 return the value of 1
in AB59. As you indicated I can conditionally format to return the 1 without
adding the ,1,0 but it seemed as it should have worked but you can see the
result.


AB

G59 H59 I59 J59 K59 L59 M59 N59 O59 P59 AB59
7 4 3 1 4 7 8 6 4 1 #VALUE!

 

[David F Cox]

did you press ctrl shift Enter to enter the formula as an array formula?

 

[Ron Rosenfeld]

Ron, thanks for helping with this formula but I still am not able to produce
the correct answer as seen in AB.

Cell AB59 has this formula in it =AND(OR(G59=K59:N59),OR(H59=K59:N59),1,0)

The logic is this. If G59 has a value found in one of the 4 cells K59:N59
and H59 has a value found in one of the 4 cells K59:N59 return the value of 1
in AB59. As you indicated I can conditionally format to return the 1 without
adding the ,1,0 but it seemed as it should have worked but you can see the
result.


AB

G59 H59 I59 J59 K59 L59 M59 N59 O59 P59 AB59
7 4 3 1 4 7 8 6 4 1 #VALUE!

Your formula will not work.

Your formula reduces to:

=AND(Condition1,Condition2,1,0)

It returns #VALUE! because you did not enter it as an **array** formula.

But even if you did, it would return FALSE since 1 & 0 cannot both be TRUE at
the same time.

This is what I wrote before:

You should be able to use the formula either to conditionally format, or if you
must have a one or zero, then precede it with a double unary:

=--AND(OR(A1=B1:B6),OR(A2=B1:B6))

It seems to me the logical translation would be to replace the cell references
I used with the one's you want. I don't understand why you added the 1 and 0
to the AND statement.

=--AND(OR(G59=K59:N59),OR(H59=K59:N59))

***ENTERED AS AN ARRAY FORMULA***

SEE MY POST FOR INSTRUCTIONS HOW TO DO THIS


--ron

 

[Dave Peterson]

And don't change Ron's formula.

Ron, thanks for helping with this formula but I still am not able to produce
the correct answer as seen in AB.

Cell AB59 has this formula in it =AND(OR(G59=K59:N59),OR(H59=K59:N59),1,0)

The logic is this. If G59 has a value found in one of the 4 cells K59:N59
and H59 has a value found in one of the 4 cells K59:N59 return the value of 1
in AB59. As you indicated I can conditionally format to return the 1 without
adding the ,1,0 but it seemed as it should have worked but you can see the
result.

AB

G59 H59 I59 J59 K59 L59 M59 N59 O59 P59 AB59
7 4 3 1 4 7 8 6 4 1 #VALUE!

 

[Guest]

yes I did, and I tried both ways without the 1,0 as Ron's first formula and
the way Excelent wrote but the return was still false. I tried to better
explain it in a different question titled Ron and/or...... I will try again
and see if there is something not correct in my formula. Thanks

 

[Guest]

I did enter it as an array formula, the braces do not copy and paste but they
are in there. The 1,0 was just added after the first formula did not return
anything but value or false when the first two numbers of the four numbers
did exist in the six number set.

 

[Ron Rosenfeld]

I did enter it as an array formula, the braces do not copy and paste but they
are in there. The 1,0 was just added after the first formula did not return
anything but value or false when the first two numbers of the four numbers
did exist in the six number set.

Adding the 1 or 0 to either of the formulas I suggested when it does not give
the expected result seems like a strange thing to do. I did not recommend
that, nor do I understand the reason behind it.

I suggest you re-read the posts, and try to follow the instructions laid out
there.

If you do, and are still not getting the expected results, the likely reason is
that some of your numbers are really text, and not numeric.

You can test that by using the ISTEXT function on each of your values.
--ron

 

[Leo Heuser]

I have four cells with numbers in each, (2)(4)(9)(1), then I have 6
adjacent
cells with numbers in each of them, (4)(3)(5)(8)(9)(2).
In a different cell, I need to represent a true statement that indicates
if
the first two numbers in the cell's content, (2) or (4), are contained in
any
of the 6 cell's content,(4)(3)(5)(8)(9)(2). If there is a match, in the
one
cell that has to represent a hit, it will place a 1 in the cell. Then
with a
conditional formula I can turn the cell red.

=if(or(a1=k1,a1=k2,a1=k3,a1=k4,a1=k5,a1=k6, ???(AND(a2=k1 and so on, , ,

How do you combine the OR and AND statement in one formula or is there
another way to do this.

Another option:

=(SUMPRODUCT((A1=K1:K6)+(A2=K1:K6))>=1)+0

 

[Guest]

Thanks Leo but I think this relies on totaling and not indicating whether or
not the number just exists. I need only to recongnize whether the forst two
( 7,8) numbers in the four set of numbers given to me exist in either the
first four numbers 1-4, the second four numbers,2-5 or the third four
numbers,3-6 in the provided 6 set and then show that as a hit ( red, true or
a number 1 ) in another set.
I tested your formula and if you rearrange the four set, the results prove
inconsistent
=(SUMPRODUCT((G61=K6161)+(H61=K6161))>=1)+0
where g61 through q61 are
7 8 3 2 6 4 1 8 3 5 1
The 7 does not exist in the six and the result is a True or as indicated
that by a 1.

 

[David Biddulph]

Have you tried changing Leo's formula to
=(SUMPRODUCT((G61=K6161)+(H61=K6161))>=2) ?

 

[Leo Heuser]

Thanks Leo but I think this relies on totaling and not indicating whether
or
not the number just exists. I need only to recongnize whether the forst
two
( 7,8) numbers in the four set of numbers given to me exist in either the
first four numbers 1-4, the second four numbers,2-5 or the third four
numbers,3-6 in the provided 6 set and then show that as a hit ( red, true
or
a number 1 ) in another set.
I tested your formula and if you rearrange the four set, the results prove
inconsistent
=(SUMPRODUCT((G61=K6161)+(H61=K6161))>=1)+0
where g61 through q61 are
7 8 3 2 6 4 1 8 3 5 1
The 7 does not exist in the six and the result is a True or as indicated
that by a 1.

In your original post you wrote "(2) or (4)", which means "one OR the other
OR both" (inclusive OR) or "one Or the other but not both (exclusive OR).

Apparently you mean "(2) AND (4)", so why don't you use Ron's formula?

Leo Heuser

 

[Leo Heuser]

Have you tried changing Leo's formula to
=(SUMPRODUCT((G61=K6161)+(H61=K6161))>=2) ?

Unfortunately, that's not enough. It would also be true, if the
sequence had 2 instances of one number and none of the other.

Leo Heuser

 

[Herbert Seidenberg]

Or combining the insight of Ron and Leo:
=SUMPRODUCT(OR(A1=K1:K6)*OR(A2=K1:K6))