PDA

View Full Version : If, And, Or



jnix612
05-24-2017, 12:07 PM
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
05-24-2017, 12:31 PM
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
05-24-2017, 12:40 PM
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
05-24-2017, 12:44 PM
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
05-24-2017, 05:52 PM
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
05-25-2017, 05:43 AM
GREAT! thank you thank you. :hi:

jnix612
08-03-2017, 08:26 AM
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
08-03-2017, 10:10 AM
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
08-03-2017, 11:30 AM
GREAT! It works. Thank you. :friends:

mdmackillop
08-03-2017, 11:46 AM
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 (http://www.ozgrid.com/Excel/nested-function-limit.htm)