Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|