# 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
###### Who is Participating?

Commented:
See this example. 5 is the median.
median.xls
0

Commented:
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

Author Commented:
Thanks very much - that does the job nicely!!
0

Commented:
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

Author 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]

0

Commented:
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

Commented:
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.