Forum Discussion
IF statement with one set condition and multiple others
In the attached excel sheet. I want to show if a Certificate is required for exporting goods from GB only to the EU or N. Ireland. So basically if goods travel from GB to either the EU or N. Ireland, then a certifcate is required (output 'Yes'), otherwise not required (output 'No').
So far I only know how to get 'Yes' if GB = EU as per the formula below.
=IF(AND(A3="GB",B3= "EU"),TRUE,FALSE)
On that, I would appreciate it if someone could tell me how to put in multiple conditions (EU or N. Ireland) for cell B3
=IF(AND(A3="GB",OR(B3={"EU","N. Ireland"})),"Yes","No")
10 Replies
If the output from the formula is to be TRUE/FALSE the IF is unnecessary.
= AND(StartingPoint="GB", OR(Destination="EU", Destination= "N. Ireland"))On the other hand, if you are willing to settle for 0/1, the final appearance of the result can be controlled by number formatting without stopping it being referenced from dependent formulae
= (StartingPoint="GB") * OR(Destination={"EU","N. Ireland"})Note: I have chosen to use defined Names to make the formula readable; it makes no difference to the calculation.
For the field "Is Catch Cert required (Y/N)" it's more logical to have an outputs as Yes/No
- Maybe. In general, I try to avoid text because is is so easy to mistype. Here I wrote to the cell with a name "Required?" and used number formatting so that it reads "Yes/No". If there are dependent formulas, they reference the cell using
= IF(required?, AdditionalResources)
rather than
= IF(required="Yes", AdditionalResources)
I seem to remember your saying you prefer 'IsRequired' for Booleans. Either way documentation is needed to help future maintenance.
Other words EU is still not "Rest of the world". Intresting.
- This is about varying the GB entry this time to N. Ireland, to either get a yes or no, and nesting it within the last formula. It may not be possible.
=IF(AND(A3="GB",OR(B3={"EU","N. Ireland"})),"Yes","No")
- many thanks, that works wery well 🙂
Apart from that, I would also need to output N. Ireland and 'EU' or 'Rest of the World' = Yes, otherwise No as an output. Would it be possible to nest this into the formula you have already supplied? I can work out other variations from there I hope!Try
=IF(OR(AND(A3="GB",OR(B3={"EU","N. Ireland"})),AND(A3="N. Ireland",OR(B3={"EU","Rest of World"}))),"Yes","No")
fishcultureman Try this one:
=IF(AND(A3="GB",OR(B3="EU",B3="N. Ireland")),TRUE,FALSE)
