Help with creating Formula again

[Benny]

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 R.]

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

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Benny" <Benny@discussions.microsoft.com> skrev i en meddelelse
news:E1D56A82-448E-49FC-BC71-EF10E2878D88@microsoft.com...
> 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]

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" <Benny@discussions.microsoft.com> skrev i en meddelelse
> news:E1D56A82-448E-49FC-BC71-EF10E2878D88@microsoft.com...
> > 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]

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

[Duke Carey]

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]

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

[Duke Carey]

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]

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

[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" <Benny@discussions.microsoft.com> skrev i en meddelelse
> news:E1D56A82-448E-49FC-BC71-EF10E2878D88@microsoft.com...
> > 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]

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" <Benny@discussions.microsoft.com> skrev i en meddelelse
> news:E1D56A82-448E-49FC-BC71-EF10E2878D88@microsoft.com...
> > 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]

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

[Duke Carey]

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

[Leo Heuser]

"Benny" <Benny@discussions.microsoft.com> skrev i en meddelelse
news:38E9D27C-1E38-4184-9F9E-2C9921FA9FF2@microsoft.com...
> 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