MedianIf Formula - Array with NA/s problem

JEB85

Board Regular
Joined
Aug 13, 2010
Messages
228
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Control+shift+enter, not just enter:
Code:
=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

Board Regular
Joined
Aug 13, 2010
Messages
228
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,493
Messages
5,601,998
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top