Weighted Median

Maybe ...

Row\Col	A	B	C	D	E
1	Qty	Wage
2	4	$11.00		$10.00	D2: {=MEDIAN(IF(COLUMN(INDIRECT("1:" & MAX(A2:A7))) <= A2:A7, B2:B7))}
3	3	$12.00
4	8	$10.00
5	2	$12.00
6	1	$17.00
7	5	$9.25

That's a weighted average, Pete.

The array in column E above appears to work. I can't thank you enough!

You're welcome.

Originally Posted by jhinkel

Is it really the case that Excel can't calculate a weighted median? I need to calculate the median wage for various job positions in an industry. [....]
I have about 700 entries, so there is no way I am going to create helper columns for this

Originally Posted by shg

Maybe [....]
D2: {=MEDIAN(IF(COLUMN(INDIRECT("1:" & MAX(A2:A7))) <= A2:A7, B2:B7))}

Originally Posted by jhinkel

The array in column E [sic] above appears to work.

Only if you want to calculate the actual median, not the weighted median.

Shg's formula calculates the actual median. Of course, that is more accurate than the weighted median. But the correctness depends on what your assignment requires.

PS.... The weighted mean makes more sense when we have data grouped in ranges; for example, 5 between 0 and 9.25, 8 between 9.25 and 10, 4 between 10 and 11, etc. Then we cannot calculate an actual mean. We must interpolate the range that contains the 50 %ile.

Moreover, shg's formula seems unnecessarily inefficient. And it works in an "xls" file (XL2003 compatible) only if the frequency of data (column A) is not more than 256.

First, for your example, the formula effectively calculates COLUMN(1:8). That is unnecessary, since that is the same as COLUMN(1:1). And COLUMN(1:1) returns 256 in an "xls" file and 16,384 in an "xlsx" or similar file (XL2007 and later).

Consequently, for your actual data ("about 700 entries"), IF(COLUMN(1:1)<=A2:A7,B2:B7) would construct a 700-by-256 array (179,200 elements) or a 700-by-16384 array (11,468,800 elements!).

Secondly, because shg's formula uses INDIRECT, Excel recalculates the array formula every time it (re)calculates anything. That can slow down data entry, especially in an "xlsx" file.

That said, there are advantages to using INDIRECT. In particular, it protects the formula from unexpected changes when the worksheet design is changed. But IMHO, it is not worth potentially 11.5 million incessant recalculations.

The following demonstrates these points....

For demonstration purposes only, I simplified the COLUMN expression, since the INDIRECT is not really needed. Also, I replaced the two instances of $12.00 with $12.01 and $12.02 so we can see where each is in the array created by the formula.

	A	B	C	D	E	F	G	H	I
1	freq	amt
2	4	$11.00
3	3	$12.01
4	8	$10.00
5	2	$12.02
6	1	$17.00
7	5	$9.25
8
9	med	$10.00	B9: {=MEDIAN(IF(COLUMN($1:$1)<=A2:A7,B2:B7))}
10
11	rows	6	B11: {=ROWS(IF(COLUMN($1:$1)<=A2:A7,B2:B7))}
12	cols	16384	C11: {=COLUMNS(IF(COLUMN($1:$1)<=A2:A7,B2:B7))}
13
14	A15:I21: {=IF(COLUMN($1:$1)<=A2:A7,B2:B7)}
15	11	11	11	11	FALSE	FALSE	FALSE	FALSE	FALSE
16	12.01	12.01	12.01	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
17	10	10	10	10	10	10	10	10	FALSE
18	12.02	12.02	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
19	17	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
20	9.25	9.25	9.25	9.25	9.25	FALSE	FALSE	FALSE	FALSE
21	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A

Formulas displayed with curly brackets {...} are array-entered, to wit: type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

Row 21 (#N/A) is outside the array. But column I is part of the array (not #N/A), presumably unintentionally. Forgive me for not displaying all 256 or 16384 columns.

I suspect shg intended the following, which creates just MAX(A2:A7) columns:

{=MEDIAN(IF(TRANSPOSE(ROW(INDIRECT("1:" & MAX(A2:A7)))) <= A2:A7, B2:B7))}

That formula still limits the frequency of data (column A), but to 65,565 in "xls" files and 1,048,576 in "xlsx" files. As large as that might sound, it might not be large enough for "job positions in an industry".

Moreover, that formula still uses INDIRECT. So it is still recalculated incessantly.

The following demonstrates how to avoid INDIRECT by using INDEX.

More importantly, it demonstrates how to calculate the weighted median, in principle. See the discussion that follows.

[.... Contintued in the next posting, due to text size limitations in this forum ....]

[.... Continued from ....]

Originally Posted by joeu2004

The following demonstrates how to avoid INDIRECT by using INDEX.

More importantly, it demonstrates how to calculate the weighted median, in principle. See the discussion that follows.

	A	B	C	D	E	F	G	H	I
1	freq	amt	wt	cuml wt
2	5	$9.25	21.74%	21.74%	C2: =A2/SUM($A$2:$A$7)
3	8	$10.00	34.78%	56.52%	D2: =SUM($C$2:C2)
4	4	$11.00	17.39%	73.91%
5	3	$12.01	13.04%	86.96%
6	2	$12.02	8.70%	95.65%
7	1	$17.00	4.35%	100.00%
8
9	wt med	$9.86	B9: =FORECAST(0.5,B2:B3,D2:D3)
10	med	$10.00	B10: {=MEDIAN(IF(TRANSPOSE(ROW(A1:INDEX(A:A,MAX(A2:A7))))<=A2:A7,B2:B7))}
11
12	rows	6	B12: {=ROWS(IF(TRANSPOSE(ROW(A1:INDEX(A:A,MAX(A2:A7))))<=A2:A7,B2:B7))}
13	cols	8	C12: {=COLUMNS(IF(TRANSPOSE(ROW(A1:INDEX(A:A,MAX(A2:A7))))<=A2:A7,B2:B7))}
14
15	A16:I22: {=IF(TRANSPOSE(ROW(A1:INDEX(A:A,MAX(A2:A7))))<=A2:A7,B2:B7)}
16	9.25	9.25	9.25	9.25	9.25	FALSE	FALSE	FALSE	#N/A
17	10	10	10	10	10	10	10	10	#N/A
18	11	11	11	11	FALSE	FALSE	FALSE	FALSE	#N/A
19	12.01	12.01	12.01	FALSE	FALSE	FALSE	FALSE	FALSE	#N/A
20	12.02	12.02	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	#N/A
21	17	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	#N/A
22	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A

IMHO, the weighted median calculation is anything but "very basic". I don't know if it a "commonly needed calculation". But I would agree it should be implemented in a function: a VBA UDF, if not an Excel built-in or Data Analysis function.

The data should be sorted by the numeric categories ("amt" in column B), and the weight factors (column C) and cumulative weight factors (column D) should be calculated. The weight factor is the category frequency divided by the sum of the frequencies, so that the sum of the weight factors is 1.

Then the weighted median is the interpolated "amt" that corresponds to a weight factor of 50%. In this example, it is between B2 and B3.

Where there are only 2 data points, FORECAST correctly interpolates along the line between them. That is, FORECAST(0.5,B2:B3,D2:D3) returns B2 + (B3-B2)*(0.5-D2)/(D3-D2).

I realize this does not meet your objective of avoiding helper cells. And it is incomplete insofar as determining the B2 and B3.

Perhaps some adventurous soul will provide a (torturous) formula to do all this. But as I said, I think it is easier and better implemented in a function.

shg's formula seems unnecessarily inefficient

joeu's post seems unnecessarily verbose.

thought I had a solution. not yet.

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Nonetheless, you still need to open your own thread, please, as explained in my post above.

This is an old thread and is now being closed.