In case you do not see the latest reply.

Thanks all but I stil receive incorrect results while testing of both types
of formulas- all are numbers and not text, array is set with braces.
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 and none of the six contain the number 1. This is for 2 of 6.

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=K60:P60,H60=K60:P60),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 although the numbers do exist. This is for 4 of 6.

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:N 59),OR(J59=K59:N59))}

As said before, braces exist, numbers are numbers. Correct results at first
but when new numbers or numbers are rearranged in the 4 set, results are
incorrect.

Did you check to see that your calculation is set to *auto*?

<Tools <Options <Calculation tab.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Shu of AZ" wrote in message
...
In case you do not see the latest reply.

Thanks all but I stil receive incorrect results while testing of both
types
of formulas- all are numbers and not text, array is set with braces.
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 and none of the six contain the number
1. This is for 2 of 6.

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=K60:P60,H60=K60:P60),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 although the numbers do
exist. This is for 4 of 6.

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:N 59),OR(J59=K59:N59))}

As said before, braces exist, numbers are numbers. Correct results at
first
but when new numbers or numbers are rearranged in the 4 set, results are
incorrect.

On Sun, 15 Oct 2006 08:56:02 -0700, Shu of AZ
wrote:

(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 although the numbers do exist. This is for 4 of 6.

This explanation of how you changed the input is completely incomprehensible to
me.

What set of numbers, that you think should give a TRUE, is giving a FALSE.

The set you posted gives a TRUE

G59 H59 I59 J59 K59 L59 M59 N59
1 2 4 7 7 4 2 1


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

And, as I wrote previously, if you want the formula to return a 1 or 0, merely
precede the above with a double-unary:

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


--ron