Median with two If statements in an array formula

[Debbie Thomson]

Good Morning,

I'm struggling with an array formula that will use two parameters. In the attached I would like to end up with the median of individual sites by week ending date.

If I had one parameter (a median of the Days Held based on the Location), I would put in the following.

={MEDIAN(IF($A$2:$A$8=A2,$M$2:$M$8))}

I would also like, as well as the Location, to look at only the max of the Week Ending.

So, in non-formula terms: where a singular location matches the Location column entries and where the Week Ending date matches the max of the Week Ending date column, return me the median based on Days Held.

Thank you very much in advance for any help given.

[ajryan88]

Hi,

Try this array formula in cell N2, then copy down as far as is necessary, adjusting the ranges as necessary also:

Please Login or Register  to view this content.

Hope this helps

[Debbie Thomson]

Amazing! Thank you VERY much for your help.

[ajryan88]

You welcome!

Please don't forget to mark this thread as solved

[RobertMika]

=MEDIAN(IF((A2=$A$2:$A$8)*(TEXT($L$2:$L$8,"yy-mm-dd")=(TEXT(L2,"yy-mm-dd"))),$M$2:$M$8))
Confirm COntrol+shift+Enter

[Debbie Thomson]

Thanks Robert.

[RobertMika]

You are welcome.