CODEWITHSUNDEEP
excelexcel-formula
Dom Fran
Dom Fran

Reputation: 47

Median for frequency distribution

I have data in excel that looks similar to the following:

|          |  1|  2|  3|  4|  5|
|----------|---|---|---|---|---|
|Question 1| 18| 20|  1|  1|  0|
|Question 2|  9| 18|  9|  4|  4|
|Question 3| 11| 10| 13|  6|  0|
|Question 4|  4| 18| 10|  8|  0|

The table shows how many people chose any of the 5 possible answers (1-5) for several questions.

I have tried the solutions that barry houdini suggested here: http://www.mrexcel.com/forum/excel-questions/530046-find-medians-frequency-distribution-table.html

But I get #N/A error.

This is the exact formula that I used for data in B9:F18 and values in B8:F8:

=SUM(LOOKUP(INT(SUM(B9:F9)/2+{0.5,1}),SUBTOTAL(39,OFFSET(A9,,,1,COLUMN(B9:F9)-COLUMN(B9)+1))+1,B$8:F$8))/2

Upvotes: 1

Views: 6383

Answers (2)

lori_m
lori_m

Reputation: 5567

The formula works nicely with 9 in place of 39, which solves the stated problem.

For the record I believe it can be shortened a little and made non-volatile which may be preferable:

=SUM(LOOKUP(INT(SUM(B9:F9)-{1,0})/2,SUMIF(B$8:F$8,"<"&B$8:F$8,B9:F9),B$8:F$8))/2

Another way to calculate the median for a frequency distribution is by linear interpolation of the cumulative frequencies. For this method you can try:

=PERCENTILE(B8:F8,PERCENTRANK(INDEX(SUMIF(B$8:F$8,"<="&B$8:F$8,B9:F9),),SUM(B9:F9)/2,30))

(Note that for this method the median is not well defined if it falls inside the first category (<1) unless you include a lower bound such as 0.)

Upvotes: 0

Marc K
Marc K

Reputation: 271

As others have noted in the comments, there was an error in your subtotal function as 39 is not valid for the first argument.

Subtotal takes the form SUBTOTAL(function_num, ref1, ref2, ...)

where "Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list." | Function_num (includes hidden values) | Function | |---------------------------------------|----------| | 1 | Average | | 2 | COUNT | | 3 | COUNTA | | 4 | MAX | | 5 | MIN | | 6 | PRODUCT | | 7 | STDEV | | 8 | STDEVP | | 9 | SUM | | 10 | VAR | | 11 | VARP |

Or 101-111 for ignoring hidden values.

See Excel SUBTOTAL function reference

Upvotes: 1

Related Questions