MedianIf Formula - Array with NA/s problem

[JEB85]

Hi,

I have the following array formula located in cell C4.

=MEDIAN(IF(TEXT($H$11:$H$1057,"mmm")="Jul",(IF($B$11:$B$1057="North East",$J$11:$J$1057)),""))

The column headers go across row 10.

Column H: List of Dates e.g. 06-Jul-10
Column B: List of Quadrants - North East, North West, South East, South West, #N/A
Column J: List of numbers e.g. 39

The formula described above works great if i custom sort my data set by quadrant and only highlight up to the last quadrant before the #N/A's begin. For column B - as you can see this goes up to 1057. However, inclusive of the #N/A's the array actually goes up to row 1063.

If i don't custom sort my data by quadrant and go up to row 1063 for the arrays the formula just returns #N/A.

This leads me to my question;

Is there a way in which i don't have to custom sort my data set, can use the entire arrays and the formula will return the median? In other words, what change do i need to make to my formula to get it to work how i've just described.

Thanks

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=MEDIAN(
    IF($H$11:$H$1057-DAY($H$11:$H$1057)+1=M2,
    IF(1-ISNA($B$11:$B$1057),
    IF($B$11:$B$1057=N2,
      $J$11:$J$1057))))

where M2 houses a month/year of interest like 1-Jul-2010 (written as a first day date) and N2 a value like North East.

 

[JEB85]

Works brilliantly, thanks Aladin!

 

[JEB85]

One quick question Aladin - you couldn't explain to me how the date part of the formula is working please. So i'm looking for the median in July...how is the formula looking at all of July's dates when in cell M2 is 01-Jul-10? I know what it's doing is right because i've used the formula and compared it to the way I had been doing it. Previously the text part of the formula was picking up all the July dates, I just can't understand how it's picking all of July's dates now if it's looking at 01-Jul-10.

Thanks again, genius formula!