Ask Your Question

Calculating median using dates [closed]

asked 2018-12-03 18:20:26 +0100

RochdaleRules gravatar image

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)

image description

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by RochdaleRules
close date 2018-12-07 06:23:20.595521

1 Answer

Sort by » oldest newest most voted

answered 2018-12-04 15:43:22 +0100

erAck gravatar image

updated 2018-12-04 15:53:12 +0100

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().

edit flag offensive delete link more


Brilliant explanation, thank you.

I now have it working.

RochdaleRules gravatar imageRochdaleRules ( 2018-12-04 16:00:16 +0100 )edit

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

erAck gravatar imageerAck ( 2018-12-04 16:22:57 +0100 )edit

Question Tools

1 follower


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

Seen: 33 times

Last updated: Dec 04