|
Combining formulas, "and" & "or" to verify content of multiple cel
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. |
Combining formulas, "and" & "or" to verify content of multiple cel
=IF(OR(A1=K1:K6,A2=K1:K6),1,0) hit CTRL+SHIFT+ENTER
"Shu of AZ" skrev: 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. |
Combining formulas, "and" & "or" to verify content of multiple cel
|
Combining formulas, "and" & "or" to verify content of multiple
did you press ctrl shift Enter to enter the formula as an array formula?
"Shu of AZ" wrote in message ... 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! "Ron Rosenfeld" wrote: On Sat, 14 Oct 2006 11:41:01 -0700, Shu of AZ <Shu of wrote: 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 |
Combining formulas, "and" & "or" to verify content of multiple
On Sat, 14 Oct 2006 16:14:01 -0700, Shu of AZ
wrote: 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 befo 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 |
Combining formulas, "and" & "or" to verify content of multiple
And don't change Ron's formula.
Shu of AZ wrote: 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! "Ron Rosenfeld" wrote: On Sat, 14 Oct 2006 11:41:01 -0700, Shu of AZ <Shu of wrote: 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 -- Dave Peterson |
Combining formulas, "and" & "or" to verify content of multiple
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 "David F Cox" wrote: did you press ctrl shift Enter to enter the formula as an array formula? "Shu of AZ" wrote in message ... 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! "Ron Rosenfeld" wrote: On Sat, 14 Oct 2006 11:41:01 -0700, Shu of AZ <Shu of wrote: 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 |
Combining formulas, "and" & "or" to verify content of multiple
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" wrote: On Sat, 14 Oct 2006 16:14:01 -0700, Shu of AZ wrote: 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 befo 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 |
Combining formulas, "and" & "or" to verify content of multiple
On Sat, 14 Oct 2006 17:05:02 -0700, Shu of AZ
wrote: 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 |
Combining formulas, "and" & "or" to verify content of multiple cel
|
Combining formulas, "and" & "or" to verify content of multiple
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=K61:P61)+(H61=K61:P61))=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. "Leo Heuser" wrote: "Shu of AZ" <Shu of skrev i en meddelelse ... 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 -- Best regards Leo Heuser Followup to newsgroup only please. |
Combining formulas, "and" & "or" to verify content of multiple
Have you tried changing Leo's formula to
=(SUMPRODUCT((G61=K61:P61)+(H61=K61:P61))=2) ? -- David Biddulph "Shu of AZ" wrote in message ... 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=K61:P61)+(H61=K61:P61))=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. "Leo Heuser" wrote: "Shu of AZ" <Shu of skrev i en meddelelse ... 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 -- Best regards Leo Heuser Followup to newsgroup only please. |
Combining formulas, "and" & "or" to verify content of multiple
"Shu of AZ" skrev i en meddelelse
... 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=K61:P61)+(H61=K61:P61))=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 |
Combining formulas, "and" & "or" to verify content of multiple
"David Biddulph" skrev i en meddelelse
... Have you tried changing Leo's formula to =(SUMPRODUCT((G61=K61:P61)+(H61=K61:P61))=2) ? -- David Biddulph 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 "Shu of AZ" wrote in message ... 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=K61:P61)+(H61=K61:P61))=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. "Leo Heuser" wrote: "Shu of AZ" <Shu of skrev i en meddelelse ... 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 -- Best regards Leo Heuser Followup to newsgroup only please. |
Combining formulas, "and" & "or" to verify content of multiple
Or combining the insight of Ron and Leo:
=SUMPRODUCT(OR(A1=K1:K6)*OR(A2=K1:K6)) |
| All times are GMT +1. The time now is 03:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com