I am trying to calculate the median, using a set of dates as a criteria.
I just cannot get it to work
In the example screenshot, I have attempted to get it to calculate the median for January. The result should be 5.
When I attempt to calculate for month = December, it calculates the median using all 4 values (including January)
You need to force the formula into array-mode context by closing it with Shift+Ctrl+Enter instead of just Enter.
Otherwise for functions and operators that take a scalar value (like MONTH()) it takes the implicit intersection of the formula position and the range reference; as the formula is on row 3 here that is A3. That’s what you observe for December because A3 contains a December date, MEDIAN() then calculates 4 values because IF() returns the entire B3:B6 range. If you entered your formula in non-array-mode in let’s say N7 that does not intersect rows 3:6 then the result would be a #VALUE! error.
In array-mode for each matching value in A3:A6 (of the IF(MONTH(A3:A6)=1,…)) the corresponding value in B3:B6 is returned and for non-matching values in A3:A6 a “non-element” as the ELSE path of IF() is empty, and the resulting matrix then is fed to MEDIAN().
Brilliant explanation, thank you.
I now have it working.
