If, And, Or

[jnix612]

This is my If statement:

=IF(AND(H14="Ghana",OR(J14="Master",J14="Doctorate", L14="Master",L14="Doctorate")),"No","Yes")

If they are from Ghana they must have a Master or Doctorate degree in one or both the J and L column, if they have the higher degree true value is No (do not reject), false is Yes (reject)

But it's not working. I'm not good with nesting IF statements. I'm surprised I got this far. Do you mind helping me correct this?

[SamT]

Only one
=IF(AND(H14="Ghana",OR(J14="Higher",L14="Higher")),"No","Yes")

Either one (Redundant but clearer, IMO)
=IF(AND(H14="Ghana",OR(OR(J14="Master",J14="Doctorate"), OR(L14="Master",L14="Doctorate"))),"No","Yes")

[jnix612]

NOPE, NOPE, I would have never figured this out. Three "or" no way. Thank you so much. I was hoping I just made a small mistake. LOL It's working great. Thank you again.

[jnix612]

Sorry I as wrong in when I said it's work (well what you gave me is working): But I think I still have something wrong in H4 "Ghana" the formula is reading all of column H as Ghana.

H4 will be a variety of countries. Only people from Ghana require the Master or Doctorate. It looks like the formula is only reading the or part of the statement.

=IF(AND(H4="Ghana",OR(OR(J4="Master",J4="Doctorate"), OR(L4="Master",L4="Doctorate"))),"No","Yes")

[SamT]

Only people from Ghana require the Master or Doctorate.

All others get a "No"

=IF(H4="Ghana",IF(OR(J4="Master",J4="Doctorate",L4="Master",L4="Doctorate") ,"No","Yes"),"No")

[jnix612]

GREAT! thank you thank you.

[jnix612]

Hi again, I need to expand the above formula you helped me with. I need to include 3 more countries, but Excel says I have entered too many arguments for this function. I need to add Nigeria, Ethiopia, Sudan. If it's too many I don't mind creating a new column. But I assume I'm not adding the new countries correctly.

=IF(H3="Ghana",IF(OR(J3="Masters",J3="Doctorate",L3="Masters",L3="Doctorate "),"No","Yes"),"No") - CORRECT FORMULA YOU GAVE ME

=IF(H3="Ghana", "Nigeria", "Ethiopia", "Sudan", IF(OR(J3="Masters",J3="Doctorate",L3="Masters",L3="Doctorate"),"No","Yes"), "No")))) - I have no clue. I love and hate nesting functions.

[SamT]

About now is when I turn to User Defined Functions.

UDFs are VBA Functions kept in a Standard Module, that you can use in Excel Formulas. They are also available to the Insert Function Icon on the Formula Editing Toolbar.
Since the question is "Do these need a degree?" I will name the Function "NeedsDegree" and it will return "Yes" or No" in the Cell the UDF is used in a Formula.

The UDF:

Option Explicit

Public Function NeedsDegree(Nation As Range, Degree1 As Range, Degree2 As Range) As String
'You can add ranges for additional Degrees allowed above, but you must check that cell in the code.

'You can edit the "DegreedNations" and the "DegreesNeeded" as desired without changing any other code
Const DegreedNations As String = "Ghana, Nigeria, Ethiopia, Sudan"
Const DegreesNeeded As String = "Masters, Doctorate"

'Check if nation needs degree
If InStr(DegreedNations, Nation) = 0 Then
  NeedsDegree = "No"
  Exit Function
End If

'Checks for required degree
If InStr(DegreesNeeded, Degree1) > 0 Then
  NeedsDegree = "No"
  Exit Function
End If

If InStr(DegreesNeeded, Degree2) > 0 Then
  NeedsDegree = "No"
  Exit Function
End If

'Nation needs degree but doesn't have one
NeedsDegree = "Yes"

End Function

Inthe cell that you have this formula in
=IF(H3="Ghana",IF(OR(J3="Masters",J3="Doctorate",L3="Masters",L3="Doctorate "),"No","Yes"),"No")

Use this formula instead
=NeedsDegree(H3,J3,L3)

You can treat that UDF formula as you would any other formula, Insert, Fill, Cut and Paste, etc

The UDF Compiles, but I did not create a test worksheet to test it.

[jnix612]

GREAT! It works. Thank you.

[mdmackillop]

Just for information, you can use Range Names for formulae which can be easier to understand with nested Ifs and the like. See here for more info