Help with creating Formula again

[Guest]

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â€

 

[Guest]

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.

 

[Leo Heuser]

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

 

[Guest]

Leo,
All I get is "Not Defined"

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.

 

[Guest]

Barb,

All I get is "NA"

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.

 

[Guest]

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"))

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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?

 

[Guest]

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

 

[Leo Heuser]

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