Forum Discussion
I'm having trouble creating a MEDIAN(IF(AND function in excel.
I'm trying to create a function that returns a median of an array when two criterion are met. The function works if I do a MEDIAN(IF( statement with one criterion. However, as soon as I change it to MEDIAN(IF(AND( the function returns 0. I tried Control + Shift + Enter, but it still does not work. I have provided an example below.
The formula for cell D1 is =MEDIAN(IF(B2:B9="a",C2:C9)), which returns the correct Median for the list.
The formula for cell D3 is =MEDIAN(IF(AND(B2:B9="a",A2:A9=6),C2:C9)), which returns the incorrect Median for the list.
| 6 | a | 1 | 3 |
| 6 | f | 78 | |
| 6 | a | 3 | 0 |
| 6 | g | 45 | |
| 6 | h | 9 | |
| 6 | a | 1 | |
| 6 | a | 34 | |
| 6 | a | 5 |
7 Replies
=MEDIAN(IF((B2:B9="a")*(A2:A9=6),C2:C9))You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- Is it possible those 'number' 6 cells are really text? I notice they are left formatted (default for text) instead of right formatted (default for numbers), but maybe you forced it that way.
FYI the reasoning to use (expression)*(expression) instead of AND(expression, expression) is because the AND will be applied to the entire combined range of ALL values while the multiplication will be applied through the index array.
