Median with complex data

[konew1]

I have column A listing individual person age in whole years. In columns B:AB I have the number of perople in each age. I want to find the median for each column. Thanks

A B
Age
47
50
51
53
54
55 1
56
57 2
58
59
60
61
62
63 1
64
65 1
66 2
67 2
68 2
69 1
70 5
71 1
72 3
73 2
74
75 2
76 1
77 1
78 3
79 1
80 2
81 3
82 3
83
84
85
86
87 1
88
89
90
94
96
Grand Total 40

 

[Robert Mika]

Excel has it ows MEDIAN function.
If what you are looking is =MEDIAN(b2:b96), that's your answer

 

[konew1]

Thanks, but I didn't explain the query adequately. Column A has a list of age (numbers from about 60 to 90) and column B has the number of people of each age group.
So if there are 2 people aged 68 years, none aged 69 and 3 aged 70 (and no other data), then on the row with 68 in column A there will be data "2" in column B, the row for age 69 will have zero and the row for age 70 will have 3. The median is 70 and is found from combining data from A and B. (This example does not come from the data posted originally because I wanted to only have a couple of data points for the example)

 

[T. Valko]

Thanks, but I didn't explain the query adequately. Column A has a list of age (numbers from about 60 to 90) and column B has the number of people of each age group.
So if there are 2 people aged 68 years, none aged 69 and 3 aged 70 (and no other data), then on the row with 68 in column A there will be data "2" in column B, the row for age 69 will have zero and the row for age 70 will have 3. The median is 70 and is found from combining data from A and B. (This example does not come from the data posted originally because I wanted to only have a couple of data points for the example)

Is this what you want to do:

Book1
	A	B	C	D
2	50	0	_	51
3	51	2	_	51
4	52	2	_	52
5	53	3	_	52
6	_	_	_	53
7	_	_	_	53
8	_	_	_	53
9	_	_	_	Median
10	_	_	_	52
Sheet1

 

[xenou]

I came up with (as a user defined funtion). Dunno if this can be done with a worksheet formula without breaking it out into discrete values to use with a simple Median formula.

[COLOR="Navy"]Function[/COLOR] Median_X(ByRef arg [COLOR="Navy"]As[/COLOR] Range) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a

    [COLOR="Navy"]ReDim[/COLOR] a(1 [COLOR="Navy"]To[/COLOR] WorksheetFunction.Sum(arg.Columns(2)))
   
    k = 1
    [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] arg.Rows.Count
        [COLOR="Navy"]For[/COLOR] j = 1 [COLOR="Navy"]To[/COLOR] arg.Cells(i, 2).Value
            a(k) = arg.Cells(i, 1).Value
            k = k + 1
        [COLOR="Navy"]Next[/COLOR] j
    [COLOR="Navy"]Next[/COLOR] i
    Median_X = WorksheetFunction.Median(a)

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

[COLOR="Navy"]Sub[/COLOR] Test_Median_X()
[COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] Median_X(Range("A1:B3"))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

<a href="http://northernocean.net/etc/mrexcel/20110612_book1.zip">Test Workbook</a>
sha256 checksum: 233e27563efcbe9ae86f87cfa6611ccc143974f3be702f334dda088ffc2d66ec

 

[T. Valko]

Dunno if this can be done with a worksheet formula without breaking it out into discrete values to use with a simple Median formula.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Age</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Count</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Median</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">50</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">52</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">51</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">52</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">53</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR></TBODY></TABLE>


Array formula entered in D2:

=MEDIAN(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(B2:B5))))<=B2:B5,A2:A5))

There's a shorter version but it's very inefficient.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[barry houdini]

That's clever.......

 

[xenou]

I quite like it too.

 

[konew1]

Thanks. I like it too. It works, and later tonight when I get some time I will try to understand how it works

 

[T. Valko]

Thanks. I like it too. It works, and later tonight when I get some time I will try to understand how it works

You're welcome. Thanks for the feedback!