And/Or Results - Ron/Excelent

[Guest]

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=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 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:N59),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.

 

[Ragdyer]

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 !
---------------------------------------------------------------------------

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.

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.came in rearranged in the four set, it went to false although the numbers do
exist. This is for 4 of 6.

 

[Ron Rosenfeld]

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:N59),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