Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
thekovinc
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Domenic
 
Posts: n/a
Default 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. :)

  #3   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.misc
thekovinc
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding the percentage difference of two or more numbers slr Excel Worksheet Functions 5 January 26th 06 03:00 PM
calculation based on meeting two criteria jerry Excel Discussion (Misc queries) 2 October 7th 05 12:35 AM
copy rows meeting criteria to another worksheet confused Excel Worksheet Functions 4 October 4th 05 11:51 AM
finding data between two numbers (1000-1999)and totaling correspo. plasticmaker Excel Discussion (Misc queries) 1 January 7th 05 08:55 PM
Finding Numbers with Cells that also contain letters Adam Excel Discussion (Misc queries) 7 December 29th 04 03:41 PM


All times are GMT +1. The time now is 10:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"