Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
optional calculations | Excel Worksheet Functions | |||
Choose 2 of 6 optional units for grade - formula question | Excel Worksheet Functions | |||
Optional hyphen? | Excel Discussion (Misc queries) | |||
Cells to be mandatory fill have a formula in it | Excel Discussion (Misc queries) | |||
Optional Linking | Excel Discussion (Misc queries) |