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?

Improve this question
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, 2017 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, 2017 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!
    – JackOfTales
    Apr 18, 2017 at 16:35

3 Answers 3

Reset to default
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"))
Improve this answer
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!
    – JackOfTales
    Apr 18, 2017 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")))
Improve this answer
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)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .