=ABS(SUMIF(A:A,"<"&A1,B:B)

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.

Solved

Posted on 2011-10-06

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

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

7 Comments

=ABS(SUMIF(A:A,"<"&A1,B:B)

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.

See this example. 5 is the median.

median.xls

median.xls

=MEDIAN(IF(TRANSPOSE(ROW(I

confirmed with CTRL+SHIFT+ENTER

regards, barry

=MEDIAN(IF(TRANSPOSE(ROW(I

( 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?

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?

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

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**24** Experts available now in Live!