CODEWITHSUNDEEP
excelif-statementconditional-statementsboolean-logicboolean-expression
sjedi
sjedi

Reputation: 87

Troubleshooting of Boolean combination of AND and OR conditions in Excel Formula

I am trying to set up an Excel conditional formula with both AND and OR criteria. However, the last 2 criteria OR(Table1[Col5]=$J$4,Table1[Col5]=$K$4) and OR(Table1[Col6]=Pivot!$J$5,Table1[Col6]=Pivot!$K$5,Table1[Col6]=Pivot!$L$5)

are not taken into account when I run the following formula.

MEDIAN(IF((Table1[Col1]=Pivot!$I8)*(Table1[Col2]>=$J$1)*(Table1[Col2]<=$K$1)*(ISBLANK(Table1[Col3])=TRUE)*(Table1[Col4]=Pivot!$J$3)*OR(Table1[Col5]=$J$4,Table1[Col5]=$K$4)*OR(Table1[Col6]=Pivot!$J$5,Table1[Col6]=Pivot!$K$5,Table1[Col6]=Pivot!$L$5),Table1[Col12]))

This means that running the above formula gives the same results as

MEDIAN(IF((Table1[Col1]=Pivot!$I8)*(Table1[Col2]>=$J$1)*(Table1[Col2]<=$K$1)*(ISBLANK(Table1[Col3])=TRUE)*(Table1[Col4]=Pivot!$J$3),Table1[Col12]))

which does not have the last 2 OR criteria.

Upvotes: 0

Views: 32

Answers (1)

Michal
Michal

Reputation: 6064

The issues you encounter is well explain in this answer, you can try the following formula using + instead of OR:

=MEDIAN(
    IF(
       (Table1[Col1]=Pivot!$I8) *
       (Table1[Col2]>=$J$1) *
       (Table1[Col2]<=$K$1) *
       (ISBLANK(Table1[Col3])=TRUE) *
       (Table1[Col4]=Pivot!$J$3) *
       ((Table1[Col5]=$J$4) + (Table1[Col5]=$K$4)) *
       ((Table1[Col6]=Pivot!$J$5) + (Table1[Col6]=Pivot!$K$5) + 
       (Table1[Col6]=Pivot!$L$5)),
    Table1[Col12]
))

Upvotes: 0

Related Questions