I am trying to combine OR conditions with AND over multiple columns in my Excel datasheet and would like advice on how to do this. A sample table is shown below.
The formula does not calculate the two OR conditions for [Year] and [Season].
=MEDIAN(IF((Table1[Fruit]="Apple")*OR(Table1[Year]=2023,Table1[Year]=2024)*OR(Table1[Season]="Summer",Table1[Season]="Spring"),Table1[Value]))
The above formula gives Median = 5. The correct answer is Median = 5.5
Sample full data
| Season | Year | Fruit | Value |
|---|---|---|---|
| Summer | 2023 | Apple | 3 |
| Winter | 2023 | Apple | 5 |
| Spring | 2023 | Banana | 10 |
| Summer | 2023 | Banana | 15 |
| Winter | 2024 | Apple | 50 |
| Spring | 2024 | Banana | 2 |
| Summer | 2024 | Apple | 8 |
| Winter | 2025 | Banana | 12 |
| Spring | 2023 | Orange | 42 |
| Summer | 2023 | Orange | 20 |
| Winter | 2024 | Orange | 9 |
| Spring | 2025 | Apple | 2 |
| Spring | 2025 | Banana | 10 |
Correct filtering of [Fruit] = Apple, [Year] = 2023 or 2024, [Season] = Summer or Spring:
Median (3,8) = 5.5
| Season | Year | Fruit | Value |
|---|---|---|---|
| Summer | 2023 | Apple | 3 |
| Summer | 2024 | Apple | 8 |
Use + instead of OR
MEDIAN(IF((Table1[Fruit]="Apple")*((Table1[Year]=2023)+(Table1[Year]=2024))
*((Table1[Season]="Summer")+(Table1[Season]="Spring")),Table1[Value]))