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((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.- That was it! The format was set to text instead of numbers. Thank you for the explanation as well. Are you able to explain the difference between an array and a range? It would be nice to know for some potential issues I may run into in the future.
The formula returns the expected result in my sheet. Can you attach a screenshot of your worksheet which shows all the necessary information (without sensitive data)?
- My cells weren't formatted correctly. Thanks for your help!
