Median of Selected Numbers

I avoid array formulas like the plague.

Why? Because they are so easily broken by other users who don't know to Ctrl+Shift+Enter and by me when I forget to.

Instead, I use the SUMPRODUCT function. It generally works the same and I don't need Ctrl+Shift+Enter.

Here are two examples:

= SUMPRODUCT (( A1:A10 > 3 ) * ( B1:B10 < 5 ), D1:D10 )

= SUMPRODUCT ( - - ( A1:A10 > 3 ), D1:D10 )

In the first example, we have two arrays of logical conditions. Each gives us an array True's and False's. When the two arrays are multiplied together, they produce an array of 1's and 0's, which SUMPRODUCT then multiplies by the array in column D. This method works with two or more conditional arrays.

In the second example, we only have one array of logical conditions. It gives us one array of True's and False's. SUMPRODUCT does not like working directly with True's and False's, so we use double negation to turn them into 1's and 0's.

Using double-negation, the first example can be re-written as:

= SUMPRODUCT ( - - ( A1:A10 > 3 ), - - ( B1:B10 < 5 ), D1:D10 )

SUMPRODUCT can also be used with logical functions like OR and AND.

PS: Double negation is a great way to turn True's and False's into numeric values for calculations.

PPS: You will notice that my formula examples included spaces. I always use spaces for readability and I have no idea why everyone doesn't. When we write an email or a letter, we all use spaces to separate the words and make them more readable. Why do so few do use spaces in formulas?

Joe, Ed,
Boolean operations (AND, OR etc) don't work with array formulae. More information is given here :
http://dailydoseofexcel.com/archives/2004/12/04/logical-operations-in-array-formulas/

Not sure why that did not work. I got it to work with this though:

{=MEDIAN(IF((DATE(YEAR(ListDate),MONTH(ListDate),1)=$A3)*(SellerType="Fresh"),Price))}

Thanks Ed, I'll give it a try

Try using "AND" instead of nesting the two "IF" functions. I didn't test it, but this is what I came up with:

{=MEDIAN(IF(AND(DATE(YEAR(ListDate),MONTH(ListDate),1)=$A3,SellerType="Fresh"),Price))}

How can you make this work for multiple conditions? I have a database of home sales and have defined "ListDate" as the range of dates properties were listed, and "Price" as the price at which they were listed for sale.
In column A, I have months, ie Oct-14, Nov-14, Dec-14 ... and I then want to use an array formula to calculate the median price. The following formula works perfectly.
{=MEDIAN(IF(DATE(YEAR(ListDate),MONTH(ListDate),1)=$A3,Price))}
But then I have another field where I have defined two types of property sellers "SellerType" and I want to only generate the median home price each month for "Fresh" seller types. The following array generated "#N/A" .... help?
{=MEDIAN(IF(DATE(YEAR(ListDate),MONTH(ListDate),1)=$A3,IF(SellerType="Fresh",Price)))}