Search code examples
excelexcel-formulaconditional-statements

Calculating New Value with OR and AND using IF Statement in Excel

I'm trying to add a new value to a field in Excel if one item from another field is true, and any item from a set of fields is true.

I've searched for solutions online but found primarily using only OR or AND statements. The one I did find using both led me to my attempt at a solution below, but it sadly didn't work.

Essentially, I want to check for the following, and add a 1 to the new field if the requirements are met, and a 0 if not.

IF(AC2=0)

AND

IF(OR(AC2=0),T2="2.1% to 2.8%",J2="8.3% to 12.1%", M2="Yes",P2="GT 10%",N2="No",Q2="No",R2="No",S2="No",I2="No"),1,0)

I want to merge these to ensure both requirements are met. The first requirement, and any of the following requirements.

I've tried the following, but end up with only "0"s.

=IF(AND(OR(AC2=0),T2="2.1% to 2.8%",J2="8.3% to 12.1%", M2="Yes",P2="GT 10%",N2="No",Q2="No",R2="No",S2="No",I2="No"),1,0)
Solution

  • I believe the general structure of the solution should be in the form:

    IF(AND(c1, OR(c2, c3, c4 ...))

    where c1 is the first condition where the first condition must be true and c2, c3, c4 ... are the conditions where any one of the conditions must be true.

    your answer seems to be of the form: IF(AND(OR(c1), c2, c3, c4 ...)

    this does not produce the same behaviour because the first because the OR only checks if c1 is true.