Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the percentage difference of two or more numbers | Excel Worksheet Functions | |||
calculation based on meeting two criteria | Excel Discussion (Misc queries) | |||
copy rows meeting criteria to another worksheet | Excel Worksheet Functions | |||
finding data between two numbers (1000-1999)and totaling correspo. | Excel Discussion (Misc queries) | |||
Finding Numbers with Cells that also contain letters | Excel Discussion (Misc queries) |