1

I'm trying to nest several columns into an If statement that utilizes both And and Or functions. I'm so far unable to come up with a function that works.

Rules:

  • If Act Conf Years OR Act Conf Months > 0 then output "Confinement"
  • If Act Prob years OR Act Prob Months > 0 then output "Probation"
  • If (Act Conf Years OR Act Conf Months > 0 ) AND (Act Prob years OR Act Prob Months >0) then output "Both"

The formula I came up with was:

IF(OR(D3>0,E3>0),"Confinement",IF(OR(G3>0,H3>0),"Probation",IF(OR(D3>0,E3>0,AND(G3>0,H3>0),"Both"))

However, that doesn't work. I know the issue is somewhere in the part after "Probation". But am not sure how to appropriately stack the OR and AND functions.

Alternatively, can this somehow be done with Power Query, Power Pivot, or some other add on?

3
  • IF(OR(D3>0,E3>0),"Confinement",IF(OR(G3>0,H3>0),"Probation",IF(And(OR(D3>0,E3>0),OR(G3>0,H3>0)),"Both"))? I think the issue is your final statement - you do Or(And) and it should be And(or(),or()) I think.
    – BruceWayne
    Apr 18 '17 at 15:11
  • Is it possible for the record to show no confinement and no probation? If not, you can simplify this to two IFs.
    – Excellll
    Apr 18 '17 at 15:55
  • I tried using And with nested Or's within and Excel still wouldn't process it. Simplifying the formula as @Excellll indicated worked perfect! Apr 18 '17 at 16:35
0

If "Neither Confinement nor Probation" is impossible in your data, you can use a simpler formula.

=IF(D3+E3>0,IF(G3+H3>0,"Both","Confinement"),"Probation")

If it is possible that the record shows neither confinement nor probation, you can use this formula:

=IF(D3+E3>0,IF(G3+H3>0,"Both","Confinement"),IF(G3+H3>0,"Probation","Neither"))
1
  • For whatever reason, neither of the other two answers stacking AND and OR functions work. I don't know if its my excel or something else. However, your second option works perfectly. Neither is definitely a result (as some of the rows aren't convicted so they aren't sentenced to anything or are sent to a diversion program or something else..mostly don't care, just need Confined, Probation, or both!). So thank you! Apr 18 '17 at 16:34
1

Your third ("Both") condition contains two OR statements (and an AND). Your formula did not include the second OR.

You want AND( OR (), OR() )

Before:

IF(OR(D3>0,E3>0),"Confinement",
IF(OR(G3>0,H3>0),"Probation",
IF(OR(D3>0,E3>0,AND(G3>0,H3>0),"Both"))

After:

IF(OR(D3>0,E3>0),"Confinement",
IF(OR(G3>0,H3>0),"Probation",
IF(AND(OR(D3>0,E3>0),OR(G3>0,H3>0)),"Both")))
0

Alternative version, without AND

IF(OR(D3>0, E3>0), IF(OR(G3>0, H3>0), "Both", "Confinement"),
   IF(OR(G3>0, H3>0), "Probation", ))

The logic is:

  • If Conf, (If Prob (too), then "Both", else it's just "Confinement"),
  • If Prob, then "Probation", (optional: else "None")

Where Conf is (Act Conf Years > 0 OR Act Conf Months > 0) Prob is (Act Prob Years > 0 OR Act Prob Months > 0)

You can make it simpler by substituting the OR part with temporary variable Conf and Prob (yes, algebra works in logic too)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.