Find medians from a frequency distribution table?

[serendip]

How do I find medians from a frequency distribution table?

So I have a table that looks roughly like this:


values: 6 5 4 3 2 1
group1: 1 3 0 0 5 1
group2: 3 4 7 0 0 0
group3: 0 0 1 3 4 2


Where the numbers in the group rows indicate how many people selected each value in the first row. There are actually several thousand rows (and 13 possible values) -- the number of people in each group varies wildly.

I have added a row that calculates the mean of that row, and I would like to add an additional column to each row that tells me the median of that row, if it was expanded out (so for group 1 it would be the median of 6,5,5,5,2,2,2,2,2,1 = 2).

I'm having a really hard time figuring out how to do this however. Macros are fine, but I'd prefer avoid them if possible. Help please?

 

[Gerald Higgins]

Hi, welcome to the board.

I'm not exactly sure what you want, but if you want Medians, what about using the =MEDIAN() function ?

 

[barry houdini]

Hello serendip, welcome to MrExcel

Assuming your values are in B2:G2 and then each group is a row below, e.g. B3:G3, B4:G4 etc. then you can use this formula in H3 for the median

=MEDIAN(LOOKUP(ROW(INDIRECT("1:"&SUM(B3:G3)))-1,SUBTOTAL(9,OFFSET(A3,0,0,1,COLUMN(B3:G3)-COLUMN(B3)+1)),B$2:G$2))

confirmed with CTRL+SHIFT+ENTER and copied down the column

There may be simpler ways available - you show the values in descending order in your example - will that be the case with your real data - could they be changed to ascending order? Which version of Excel are you using?

 

[serendip]

Gerald -- the median() function doesn't work here because you actually have to list out all your values (like i did in my parentheses, like 6,5,5,5,2,2,2,2,2,1 only 1 per cell), it won't calculate from a frequency distribution table like this.

Barry -- that works! I probably can't re-order the data (there's a lot of stuff being done with this data, and not all by me, nor am I the source of it, so I need the data to match everyone else's) -- but does it matter? Would you mind walking me through what this formula is doing?

I'm in Excel 2007 (on a PC, obviously)

 

[Gerald Higgins]

serendip - I may be missing something about what you are trying to do, but in general you absolutely do not have to list the individual values within the MEDIAN formula.

For example, you can use

=median(a1:a10)

to return the median from that range.

 

[serendip]

I may be missing something about what you are trying to do, but in general you absolutely do not have to list the individual values within the MEDIAN formula.

You don't have to have them listed, but you have to have them selected in your range. But I don't have each value listed in any range -- I have a row which has values, and i have other rows which list how many times each value occurred. So for group 1, the value "6" occurred 1 time, the value "5" occurred 3 times, etc -- but if i just did a median on the values in the group 1 row, it wouldn't reflect that, it would do a median of the values (1,3,0,0,5,1) instead of the actual data, which is (6,5,5,5,2,2,2,2,2,1). You actually have to have all the values listed out somewhere for the median function to work, not just a frequency distribution chart, as I have.

 

[barry houdini]

Actually, the sorting was a red herring....disregard that

overall that formula actually generates your array, i.e. 6,5,5,5,2,2,2,2,2,1 and then just takes the MEDIAN....but obviously generating the array is the tricky part....

This part

OFFSET(A3,,,1,COLUMN(B3:G3)-COLUMN(B3)+1)

generates an array of ranges of increasing size, i.e. A3, A3:B3, A3:C3 etc. then SUBTOTAL sums the values in each range, so those two parts together give you this array for group 1 in your example

{0,1,4,4,4,9} so the formula becomes

=MEDIAN(LOOKUP(ROW(INDIRECT("1:"&SUM(B3:G3)))-1,{0,1,4,4,4,9},B$2:G$2))

and the ROW(INDIRECT part generates a sequence of integers beginning at zero up to 1 fewer than the total in B3:G3 so that gives you

=MEDIAN(LOOKUP({0;1;2;3;4;5;6;7;8;9},{0,1,4,4,4,9},B$2:G$2))

Now for each LOOKUP value {0;1;2;3;4;5;6;7;8;9} that matches with the greatest value that's smaller or equal to the lookup value in the second array {0,1,4,4,4,9}, so that gives you

{0;1;1;1;4;4;4;4;4;9}

Those in turn match with the respective values in B2:G2 which gives you

{B2,C2,C2,C2,F2,F2,F2,F2,F2,G2}

[the 4s match with the last 4]

or

{6,5,5,5,2,2,2,2,2,1}

and you then take the median......

I didn't say before but column A shouldn't contain numbers (can be empty or text)

A similar but slightly different way just takes the middle two values (or the middle value twice) and then works out the MEDIAN from that by averaging those two values - this one has the advantage that it doesn't need array entering

=SUM(LOOKUP(INT(SUM(B3:G3)/2+{0.5,1}),SUBTOTAL(9,OFFSET(A3,,,1,COLUMN(B3:G3)-COLUMN(B3)+1))+1,B$2:G$2))/2

 

[serendip]

Thanks so much for the explanation, and the alternate formula! I really appreciate you taking the time to do that.

 

[barry houdini]

Further clarification:

In fact the sorting is an issue with that second formula - for that to work then B2:G2 must be sorted.....ascending or descending it doesn't matter