Troubleshooting of Boolean combination of AND and OR conditions in Excel Formula [duplicate]

Question

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.

Show us some data sample, using text (you can use a Markdown table generator ); also give us your current output and your expected output. — Excellor
– Excellor, Commented Jan 2 at 8:22
The short answer is that you need to use '+' in an array formula to get an 'OR' relationship, in the same way as you have used '*' to get AND. It's likely that the OR parts of your formula are always evaluating to TRUE, so they have no effect. — Tom Sharpe
– Tom Sharpe, Commented Jan 2 at 9:21

Michal · Accepted Answer · 2025-01-02 09:08:38Z

0

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]
))

answered Jan 2 at 9:08

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Troubleshooting of Boolean combination of AND and OR conditions in Excel Formula [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related