# Median for Groups in Excel, is there a Function?

1. ## 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

2. ## 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
``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?

3. ## 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.

4. ## 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?

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

I overlooked that, thank you it works great.!

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

#### 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