|
Help with creating Formula again
Need help with the following 2 situations involving data contained in 2 fields
to create a Yes or No field according to the following: Situation #1 IF cell D2={1,2,3,4} and cell R2=1 then =Yes And also if cell D2={1,2} and cell R2=0 then is also = Yes Situation #2 Cell D2={5,7} and cell R2={0,1} then = No -- Benny |
Try this (it's ugly)
=IF(AND(OR(D2=1,D2=2,D2=3,D2=4),R2=1),"YES",IF(AND (OR(D2=1,D2=2),R2=0),"YES",IF(AND(OR(D2=5,D2=7),OR (R2=0,R2=1)),"NO",NA()))) It will put NA() in if nothing matches. "Benny" wrote: Need help with the following 2 situations involving data contained in 2 fields to create a Yes or No field according to the following: Situation #1 IF cell D2={1,2,3,4} and cell R2=1 then =Yes And also if cell D2={1,2} and cell R2=0 then is also = Yes Situation #2 Cell D2={5,7} and cell R2={0,1} then = No -- Benny |
One way:
=IF(AND(OR(D2={5,7}),OR(R2={0,1})),"No",IF(OR(AND( OR(D2={1,2,3,4}),R2=1), AND(OR(D2={1,2}),R2=0)),"Yes","Not defined!")) -- Best Regards Leo Heuser Followup to newsgroup only please. "Benny" skrev i en meddelelse ... Need help with the following 2 situations involving data contained in 2 fields to create a "Yes" or "No" field according to the following: Situation #1 IF cell D2={1,2,3,4} and cell R2=1 then ="Yes" And also if cell D2={1,2} and cell R2=0 then is also = "Yes" Situation #2 Cell D2={5,7} and cell R2={0,1} then = "No" -- Benny |
Leo,
All I get is "Not Defined" "Leo Heuser" wrote: One way: =IF(AND(OR(D2={5,7}),OR(R2={0,1})),"No",IF(OR(AND( OR(D2={1,2,3,4}),R2=1), AND(OR(D2={1,2}),R2=0)),"Yes","Not defined!")) -- Best Regards Leo Heuser Followup to newsgroup only please. "Benny" skrev i en meddelelse ... Need help with the following 2 situations involving data contained in 2 fields to create a "Yes" or "No" field according to the following: Situation #1 IF cell D2={1,2,3,4} and cell R2=1 then ="Yes" And also if cell D2={1,2} and cell R2=0 then is also = "Yes" Situation #2 Cell D2={5,7} and cell R2={0,1} then = "No" -- Benny |
Barb,
All I get is "NA" "Barb R." wrote: Try this (it's ugly) =IF(AND(OR(D2=1,D2=2,D2=3,D2=4),R2=1),"YES",IF(AND (OR(D2=1,D2=2),R2=0),"YES",IF(AND(OR(D2=5,D2=7),OR (R2=0,R2=1)),"NO",NA()))) It will put NA() in if nothing matches. "Benny" wrote: Need help with the following 2 situations involving data contained in 2 fields to create a Yes or No field according to the following: Situation #1 IF cell D2={1,2,3,4} and cell R2=1 then =Yes And also if cell D2={1,2} and cell R2=0 then is also = Yes Situation #2 Cell D2={5,7} and cell R2={0,1} then = No -- Benny |
This formula returns "yes" for either of the two cases in Situation #1, and
"No" for all other cases =IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes","No") If you only want "No" to come up in the circumstance you describe in Situation #2, then we need to amend the formula to this =IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes",IF(AND(NOT(ISERROR(MATCH(D2,{5,7},FALSE) )),NOT(ISERROR(MATCH(R2,{1,2},FALSE)))),"No","Not Defined")) "Benny" wrote: Need help with the following 2 situations involving data contained in 2 fields to create a Yes or No field according to the following: Situation #1 IF cell D2={1,2,3,4} and cell R2=1 then =Yes And also if cell D2={1,2} and cell R2=0 then is also = Yes Situation #2 Cell D2={5,7} and cell R2={0,1} then = No -- Benny |
Mr. Duke Carey,
I tried and with the first formula all I get is "No". The second formula returns "Not Defined". Should I create a column for each situation? Thanks for your help. "Duke Carey" wrote: This formula returns "yes" for either of the two cases in Situation #1, and "No" for all other cases =IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes","No") If you only want "No" to come up in the circumstance you describe in Situation #2, then we need to amend the formula to this =IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes",IF(AND(NOT(ISERROR(MATCH(D2,{5,7},FALSE) )),NOT(ISERROR(MATCH(R2,{1,2},FALSE)))),"No","Not Defined")) "Benny" wrote: Need help with the following 2 situations involving data contained in 2 fields to create a Yes or No field according to the following: Situation #1 IF cell D2={1,2,3,4} and cell R2=1 then =Yes And also if cell D2={1,2} and cell R2=0 then is also = Yes Situation #2 Cell D2={5,7} and cell R2={0,1} then = No -- Benny |
Benny - are you sure the cells you are testing are D2 and R2? The formulas I
provided are looking at precisely those 2 cells. If you have your data in other cells, then all you'd get is a "No" with the first one and a "Not Defined" with the second one. Double check please, because both formulas work fine in my spreadsheet Duke "Benny" wrote: Mr. Duke Carey, I tried and with the first formula all I get is "No". The second formula returns "Not Defined". Should I create a column for each situation? Thanks for your help. "Duke Carey" wrote: This formula returns "yes" for either of the two cases in Situation #1, and "No" for all other cases =IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes","No") If you only want "No" to come up in the circumstance you describe in Situation #2, then we need to amend the formula to this =IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes",IF(AND(NOT(ISERROR(MATCH(D2,{5,7},FALSE) )),NOT(ISERROR(MATCH(R2,{1,2},FALSE)))),"No","Not Defined")) "Benny" wrote: Need help with the following 2 situations involving data contained in 2 fields to create a Yes or No field according to the following: Situation #1 IF cell D2={1,2,3,4} and cell R2=1 then =Yes And also if cell D2={1,2} and cell R2=0 then is also = Yes Situation #2 Cell D2={5,7} and cell R2={0,1} then = No -- Benny |
I left work already so I have to wait until tomorrow to make sure
I was giving you the right cell #s. I'd just tried it at home and it does work. Thanks a lot. This formula was driving me crazy. Thank you again. "Duke Carey" wrote: Benny - are you sure the cells you are testing are D2 and R2? The formulas I provided are looking at precisely those 2 cells. If you have your data in other cells, then all you'd get is a "No" with the first one and a "Not Defined" with the second one. Double check please, because both formulas work fine in my spreadsheet Duke "Benny" wrote: Mr. Duke Carey, I tried and with the first formula all I get is "No". The second formula returns "Not Defined". Should I create a column for each situation? Thanks for your help. "Duke Carey" wrote: This formula returns "yes" for either of the two cases in Situation #1, and "No" for all other cases =IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes","No") If you only want "No" to come up in the circumstance you describe in Situation #2, then we need to amend the formula to this =IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes",IF(AND(NOT(ISERROR(MATCH(D2,{5,7},FALSE) )),NOT(ISERROR(MATCH(R2,{1,2},FALSE)))),"No","Not Defined")) "Benny" wrote: Need help with the following 2 situations involving data contained in 2 fields to create a Yes or No field according to the following: Situation #1 IF cell D2={1,2,3,4} and cell R2=1 then =Yes And also if cell D2={1,2} and cell R2=0 then is also = Yes Situation #2 Cell D2={5,7} and cell R2={0,1} then = No -- Benny |
Leo,
I believe I was giving you the wrong cell reference before. I tried your formula at home and it worked fine. Thanks a lot for your help. "Leo Heuser" wrote: One way: =IF(AND(OR(D2={5,7}),OR(R2={0,1})),"No",IF(OR(AND( OR(D2={1,2,3,4}),R2=1), AND(OR(D2={1,2}),R2=0)),"Yes","Not defined!")) -- Best Regards Leo Heuser Followup to newsgroup only please. "Benny" skrev i en meddelelse ... Need help with the following 2 situations involving data contained in 2 fields to create a "Yes" or "No" field according to the following: Situation #1 IF cell D2={1,2,3,4} and cell R2=1 then ="Yes" And also if cell D2={1,2} and cell R2=0 then is also = "Yes" Situation #2 Cell D2={5,7} and cell R2={0,1} then = "No" -- Benny |
Leo,
I just found out that the worksheet that I was triying to use your formula on is originated from an "external data" using Excel to query a database in our server at work. And it doesn't work on it. But if I copy the results (without the column headings) and paste them in a clean worksheet, it works. I didn't know that. Do you know how to work around that? "Leo Heuser" wrote: One way: =IF(AND(OR(D2={5,7}),OR(R2={0,1})),"No",IF(OR(AND( OR(D2={1,2,3,4}),R2=1), AND(OR(D2={1,2}),R2=0)),"Yes","Not defined!")) -- Best Regards Leo Heuser Followup to newsgroup only please. "Benny" skrev i en meddelelse ... Need help with the following 2 situations involving data contained in 2 fields to create a "Yes" or "No" field according to the following: Situation #1 IF cell D2={1,2,3,4} and cell R2=1 then ="Yes" And also if cell D2={1,2} and cell R2=0 then is also = "Yes" Situation #2 Cell D2={5,7} and cell R2={0,1} then = "No" -- Benny |
Duke,
I just found out that the worksheet that I was triying to use your formula on is originated from an "external data" using Excel to query a database in our server at work. And it doesn't work on it. But if I copy the results (without the column headings) and paste them in a clean worksheet, it works. I didn't know that. Do you know how to work around that? "Duke Carey" wrote: Benny - are you sure the cells you are testing are D2 and R2? The formulas I provided are looking at precisely those 2 cells. If you have your data in other cells, then all you'd get is a "No" with the first one and a "Not Defined" with the second one. Double check please, because both formulas work fine in my spreadsheet Duke "Benny" wrote: Mr. Duke Carey, I tried and with the first formula all I get is "No". The second formula returns "Not Defined". Should I create a column for each situation? Thanks for your help. "Duke Carey" wrote: This formula returns "yes" for either of the two cases in Situation #1, and "No" for all other cases =IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes","No") If you only want "No" to come up in the circumstance you describe in Situation #2, then we need to amend the formula to this =IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes",IF(AND(NOT(ISERROR(MATCH(D2,{5,7},FALSE) )),NOT(ISERROR(MATCH(R2,{1,2},FALSE)))),"No","Not Defined")) "Benny" wrote: Need help with the following 2 situations involving data contained in 2 fields to create a Yes or No field according to the following: Situation #1 IF cell D2={1,2,3,4} and cell R2=1 then =Yes And also if cell D2={1,2} and cell R2=0 then is also = Yes Situation #2 Cell D2={5,7} and cell R2={0,1} then = No -- Benny |
Sorry Benny, I'm stumped on that one.
BTW, Leo's formula is a little trimmer than mine & therefore a little easier to use. Suggest you adopt it, once you figure out your other issue "Benny" wrote: Duke, I just found out that the worksheet that I was triying to use your formula on is originated from an "external data" using Excel to query a database in our server at work. And it doesn't work on it. But if I copy the results (without the column headings) and paste them in a clean worksheet, it works. I didn't know that. Do you know how to work around that? "Duke Carey" wrote: Benny - are you sure the cells you are testing are D2 and R2? The formulas I provided are looking at precisely those 2 cells. If you have your data in other cells, then all you'd get is a "No" with the first one and a "Not Defined" with the second one. Double check please, because both formulas work fine in my spreadsheet Duke "Benny" wrote: Mr. Duke Carey, I tried and with the first formula all I get is "No". The second formula returns "Not Defined". Should I create a column for each situation? Thanks for your help. "Duke Carey" wrote: This formula returns "yes" for either of the two cases in Situation #1, and "No" for all other cases =IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes","No") If you only want "No" to come up in the circumstance you describe in Situation #2, then we need to amend the formula to this =IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes",IF(AND(NOT(ISERROR(MATCH(D2,{5,7},FALSE) )),NOT(ISERROR(MATCH(R2,{1,2},FALSE)))),"No","Not Defined")) "Benny" wrote: Need help with the following 2 situations involving data contained in 2 fields to create a Yes or No field according to the following: Situation #1 IF cell D2={1,2,3,4} and cell R2=1 then =Yes And also if cell D2={1,2} and cell R2=0 then is also = Yes Situation #2 Cell D2={5,7} and cell R2={0,1} then = No -- Benny |
"Benny" skrev i en meddelelse
... Leo, I believe I was giving you the wrong cell reference before. I tried your formula at home and it worked fine. Thanks a lot for your help. You're welcome, Benny, and thanks for the feedback :-) LeoH |
| All times are GMT +1. The time now is 10:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com