Finding the median of numbers meeting criteria
 

I have a problem I would love some help with!

I have about 100 numbers in a column. I also have criteria in other
columns that classify the numbers into groups. I am curious if there
is any way to find the median of only SOME of the numbers in the
column.

For example:

A B C
city street 1600
city avenue 1400
town street 1500
village avenue 1700
city street 1900
city street 1200

Is there any formula I could put in a cell that would let me find the
median of the values of column C that meet the criteria of having
column A=city and column B = street (would be the median of 1600,
1900, and 1200, and would result in 1600)?

I have tried using sumproducts for the criteria aspect of it, but I
quickly got stuck.

If anyone has any ideas, I would love to hear them!

Thanks for your time. :)


--
thekovinc
------------------------------------------------------------------------
thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378
View this thread: http://www.excelforum.com/showthread...hreadid=509159

Finding the median of numbers meeting criteria
 

Try...

=MEDIAN(IF(A1:A6="City",IF(B1:B6="Street",C1:C6)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
thekovinc
wrote:

I have a problem I would love some help with!

I have about 100 numbers in a column. I also have criteria in other
columns that classify the numbers into groups. I am curious if there
is any way to find the median of only SOME of the numbers in the
column.

For example:

A B C
city street 1600
city avenue 1400
town street 1500
village avenue 1700
city street 1900
city street 1200

Is there any formula I could put in a cell that would let me find the
median of the values of column C that meet the criteria of having
column A=city and column B = street (would be the median of 1600,
1900, and 1200, and would result in 1600)?

I have tried using sumproducts for the criteria aspect of it, but I
quickly got stuck.

If anyone has any ideas, I would love to hear them!

Thanks for your time. :)

Finding the median of numbers meeting criteria
 

=MEDIAN(IF((A1:A6="city")*(B1:B6="street"),C1:C6))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

thekovinc wrote:

I have a problem I would love some help with!

I have about 100 numbers in a column. I also have criteria in other
columns that classify the numbers into groups. I am curious if there
is any way to find the median of only SOME of the numbers in the
column.

For example:

A B C
city street 1600
city avenue 1400
town street 1500
village avenue 1700
city street 1900
city street 1200

Is there any formula I could put in a cell that would let me find the
median of the values of column C that meet the criteria of having
column A=city and column B = street (would be the median of 1600,
1900, and 1200, and would result in 1600)?

I have tried using sumproducts for the criteria aspect of it, but I
quickly got stuck.

If anyone has any ideas, I would love to hear them!

Thanks for your time. :)

--
thekovinc
------------------------------------------------------------------------
thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378
View this thread: http://www.excelforum.com/showthread...hreadid=509159

--

Dave Peterson

Finding the median of numbers meeting criteria
 

Thanks! That worked perfectly!


--
thekovinc
------------------------------------------------------------------------
thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378
View this thread: http://www.excelforum.com/showthread...hreadid=509159