Ask Your Question

# Calculating median using dates [closed]

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) edit retag reopen merge delete

## 1 Answer

Sort by » oldest newest most voted 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().

more

## Comments

Brilliant explanation, thank you.

I now have it working.

@RochdaleRules Please upvote and mark the answer as correct by clicking its check mark. Thanks.

## Stats

Asked: 2018-12-03 18:20:26 +0200

Seen: 80 times

Last updated: Dec 04 '18