Mandatory and optional formula
 

I am trying to figure out how to write a formula where certain things are
mandatory and others are optional. Say I have cells A1, A2, A3, A4 and A5.
A5 is where I want the result that will either be "N" or "PRE".

I want a formula that says If EITHER A1 and A2 equal "N' or "PRE" and if
BOTH A3 and A4 equal "N" or "PRE", then A5 will equal "PRE", otherwise A5
will equal "N".

I assume there is a way to nest an if/and with an "if/or" but can't seem to
get it to work!

Mandatory and optional formula
 

"Patrick" wrote:
I want a formula that says If EITHER A1 and A2 equal "N' or "PRE" and if
BOTH A3 and A4 equal "N" or "PRE", then A5 will equal "PRE", otherwise A5
will equal "N".

In A5:

=IF(AND(OR(A1={"n","pre"},A2={"n","pre"}), A3={"n","pre"}, A4={"n","pre"}),
"PRE", "N")


----- original message -----

"Patrick" wrote in message
...
I am trying to figure out how to write a formula where certain things are
mandatory and others are optional. Say I have cells A1, A2, A3, A4 and
A5.
A5 is where I want the result that will either be "N" or "PRE".

I want a formula that says If EITHER A1 and A2 equal "N' or "PRE" and if
BOTH A3 and A4 equal "N" or "PRE", then A5 will equal "PRE", otherwise A5
will equal "N".

I assume there is a way to nest an if/and with an "if/or" but can't seem
to
get it to work!

Mandatory and optional formula
 

Try:

=IF(AND(OR(COUNTIF(A1:A2,"Pre")0,COUNTIF(A1:A2,"N ")0),OR(COUNTIF(A3:A4,"Pre")=2,COUNTIF(A3:A4,"N") =2)),"PRE","N")

HTH
Peter

"Patrick" wrote:

I am trying to figure out how to write a formula where certain things are
mandatory and others are optional. Say I have cells A1, A2, A3, A4 and A5.
A5 is where I want the result that will either be "N" or "PRE".

I want a formula that says If EITHER A1 and A2 equal "N' or "PRE" and if
BOTH A3 and A4 equal "N" or "PRE", then A5 will equal "PRE", otherwise A5
will equal "N".

I assume there is a way to nest an if/and with an "if/or" but can't seem to
get it to work!

Mandatory and optional formula
 

Errata....

I wrote:
=IF(AND(OR(A1={"n","pre"},A2={"n","pre"}), A3={"n","pre"},
A4={"n","pre"}), "PRE", "N")

That should be:

=IF(AND(OR(A1={"n","pre"},A2={"n","pre"}), OR(A3={"n","pre"}),
OR(A4={"n","pre"})),
"PRE", "N")

Also, when you wrote ``BOTH A3 and A4 equal "N" or "PRE"``, did you mean:

1. A3 is "N" or "PRE", and A4 is "N" or "PRE"? (My interpretation.)

2. A3 and A4 are both "N" or, A3 and A4 are both "PRE"? (Another
interpretation.)

Test solutions with the combination A3="N" and A4="PRE" to be sure you are
getting what you intended.


----- original message -----

"JoeU2004" <joeu2004 wrote in message
...
"Patrick" wrote:
I want a formula that says If EITHER A1 and A2 equal "N' or "PRE" and if
BOTH A3 and A4 equal "N" or "PRE", then A5 will equal "PRE", otherwise A5
will equal "N".

In A5:

=IF(AND(OR(A1={"n","pre"},A2={"n","pre"}), A3={"n","pre"},
A4={"n","pre"}), "PRE", "N")


----- original message -----

"Patrick" wrote in message
...
I am trying to figure out how to write a formula where certain things are
mandatory and others are optional. Say I have cells A1, A2, A3, A4 and
A5.
A5 is where I want the result that will either be "N" or "PRE".

I want a formula that says If EITHER A1 and A2 equal "N' or "PRE" and if
BOTH A3 and A4 equal "N" or "PRE", then A5 will equal "PRE", otherwise A5
will equal "N".

I assume there is a way to nest an if/and with an "if/or" but can't seem
to
get it to work!

Mandatory and optional formula
 

Joel, the number 2 option is whay I believe I need. In other words, BOTH A3
and A4 must be either "PRE" or "N" or the result in A5 will be "N". I know
it is confusing but I appreciate the fgact that you have me on the right path!

"JoeU2004" wrote:

Errata....

I wrote:
=IF(AND(OR(A1={"n","pre"},A2={"n","pre"}), A3={"n","pre"},
A4={"n","pre"}), "PRE", "N")

That should be:

=IF(AND(OR(A1={"n","pre"},A2={"n","pre"}), OR(A3={"n","pre"}),
OR(A4={"n","pre"})),
"PRE", "N")

Also, when you wrote ``BOTH A3 and A4 equal "N" or "PRE"``, did you mean:

1. A3 is "N" or "PRE", and A4 is "N" or "PRE"? (My interpretation.)

2. A3 and A4 are both "N" or, A3 and A4 are both "PRE"? (Another
interpretation.)

Test solutions with the combination A3="N" and A4="PRE" to be sure you are
getting what you intended.


----- original message -----

"JoeU2004" <joeu2004 wrote in message
...
"Patrick" wrote:
I want a formula that says If EITHER A1 and A2 equal "N' or "PRE" and if
BOTH A3 and A4 equal "N" or "PRE", then A5 will equal "PRE", otherwise A5
will equal "N".

In A5:

=IF(AND(OR(A1={"n","pre"},A2={"n","pre"}), A3={"n","pre"},
A4={"n","pre"}), "PRE", "N")


----- original message -----

"Patrick" wrote in message
...
I am trying to figure out how to write a formula where certain things are
mandatory and others are optional. Say I have cells A1, A2, A3, A4 and
A5.
A5 is where I want the result that will either be "N" or "PRE".

I want a formula that says If EITHER A1 and A2 equal "N' or "PRE" and if
BOTH A3 and A4 equal "N" or "PRE", then A5 will equal "PRE", otherwise A5
will equal "N".

I assume there is a way to nest an if/and with an "if/or" but can't seem
to
get it to work!


.

Mandatory and optional formula
 

I will give that a try. Thanks!

"Billy Liddel" wrote:

Try:

=IF(AND(OR(COUNTIF(A1:A2,"Pre")0,COUNTIF(A1:A2,"N ")0),OR(COUNTIF(A3:A4,"Pre")=2,COUNTIF(A3:A4,"N") =2)),"PRE","N")

HTH
Peter

"Patrick" wrote:

I am trying to figure out how to write a formula where certain things are
mandatory and others are optional. Say I have cells A1, A2, A3, A4 and A5.
A5 is where I want the result that will either be "N" or "PRE".

I want a formula that says If EITHER A1 and A2 equal "N' or "PRE" and if
BOTH A3 and A4 equal "N" or "PRE", then A5 will equal "PRE", otherwise A5
will equal "N".

I assume there is a way to nest an if/and with an "if/or" but can't seem to
get it to work!