Calculating the Median of a data set

I have a 2 column data set.
Column A contains the value
Column B contains the number of occurences of that value
How can I quickly determine the median value.

Thanks,

Andrew
AndrewMcLaughlinAsked:
Who is Participating?
 
TommySzalapskiConnect With a Mentor Commented:
See this example. 5 is the median.
median.xls
0
 
TommySzalapskiCommented:
Hmm... Good question. If you add a third column with this formula (assuming values in A and counts in B)
=ABS(SUMIF(A:A,"<"&A1,B:B)-SUMIF(A:A,">"&A1,B:B))-B1

Then anything that comes out to <0 in that column is the median. If two cells come out to exactly 0, then the average of the two is the median.
0
 
AndrewMcLaughlinAuthor Commented:
Thanks very much - that does the job nicely!!
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
barry houdiniCommented:
You can use a single formula, i.e.

=MEDIAN(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(B1:B5))))<=B1:B5,A1:A5))

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 
AndrewMcLaughlinAuthor Commented:
Hi, I get a #NUM! error when I try to use this


=MEDIAN(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(B234:B236))))<=B234:B236,A234:A236))

( this was just a dummy on a small range of data in cells A234:B236 with values in col A and counts in column B]

Can anyone advise?
0
 
TommySzalapskiCommented:
Did you forget to do Ctrl+Shift+Enter to put the formula in the cell?
It's an array formula so instead of just hitting enter to put in there, you have to be holding down the ctrl and shift keys when you hit enter.

If that's not the problem, then what's the data in the cell?
0
 
barry houdiniCommented:
Hello Andrew, I can't replicate #NUM! error for that formula (unless you have a #NUM! error in one of the referenced ranges).

See example attached of the working formula. Values in the "count" column are limited by the use of ROW function to the number of rows in the worksheet, i.e. 2^16=65536 in Excel 2003 or 2^20= 1048576 in Excel 2007 or later. If you exceed those values you'll get a #REF! error.

regards, barry
median-barry.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.