Finding the median of numbers meeting criteria

[thekovinc]

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.

[Domenic]

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 <[email protected]>,
thekovinc <[email protected]>
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.

[Dave Peterson]

=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

[thekovinc]

Thanks! That worked perfectly!