Mandatory and optional formula

  • Thread starter Thread starter Patrick
  • Start date Start date
P

Patrick

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!
 
Patrick said:
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".
Click to expand...

In A5:

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


----- original message -----
 
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
 
Errata....
=IF(AND(OR(A1={"n","pre"},A2={"n","pre"}), A3={"n","pre"},
A4={"n","pre"}), "PRE", "N")
Click to expand...

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 -----
 
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!
 
I will give that a try. Thanks!

Billy Liddel said:
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
Click to expand...
 
Back
Top