+ Reply to Thread
Results 1 to 5 of 5

Median for Groups in Excel, is there a Function?

  1. #1
    Registered User
    Join Date
    07-11-2009
    Location
    -
    MS-Off Ver
    Excel 2003
    Posts
    46

    Median for Groups in Excel, is there a Function?

    Hello,
    I have a simple project that I am working on
    In Column A there is a list composed of cities that appear multiple times
    In Column B there are temperature readings.

    so its like this

    Cities------------Temperature
    New York---------10
    Los Angeles ------30
    Tampa -----------70
    New York---------100
    New York---------90
    Los Angeles-------80

    etc

    I would like to calculate in Column C, the median value of the group of temperature readings associated to the city in that row.

    i.e. the output should look like this

    Column A-----Column B--Column C
    New York----------10-------------90
    Los Angeles--------30-------------60
    Tampa---------------70-------------- 70
    New York----------100------------90
    New York----------90-------------90
    Los Angeles--------80------------ 60

    Thanks, I would appreciate your help
    Last edited by ej463; 07-11-2009 at 01:26 PM. Reason: columns didnt appear right

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Median for Groups in Excel, is there a Function?

    As much as I loathe array formulas, that's what's required here:
    Using your posted data in A1:B6

    Enter this array formula, committed with CTRL+SHIFT+ENTER
    (instead of using just ENTER):
    Please Login or Register  to view this content.
    Copy C1 and paste into C2:C6

    These are the returned values in C1:C6:

    Please Login or Register  to view this content.
    Note: the median for Los Angeles is 55...not 60.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    07-11-2009
    Location
    -
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: Median for Groups in Excel, is there a Function?

    Thank you very much for your help. Unfortunately, I need exact values for the median, i have 2000 records many of which are measurements that are in tenths or hundredths. As a result I believe I would need an array formula that is more accurate. Im not too proficient with excel, so I would greatly appreciate a more precise formula.
    Last edited by ej463; 07-11-2009 at 04:29 PM.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Median for Groups in Excel, is there a Function?

    Unfortunately, I need exact values for the median
    Ron's suggestion gives 15 digits of precision -- is that close enough?
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    07-11-2009
    Location
    -
    MS-Off Ver
    Excel 2003
    Posts
    46

    Re: Median for Groups in Excel, is there a Function?

    I overlooked that, thank you it works great.!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1