• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

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
0
AndrewMcLaughlin
Asked:
AndrewMcLaughlin
  • 3
  • 2
  • 2
1 Solution
 
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
 
TommySzalapskiCommented:
See this example. 5 is the median.
median.xls
0
 
AndrewMcLaughlinAuthor Commented:
Thanks very much - that does the job nicely!!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now