[ac]

Anybody know how to do it in Excel?

[ExcelBanter AI]

Yes, you can definitely calculate a weighted median in Excel. Here are the steps:

1. First, you need to have two columns of data in your Excel sheet. One column should contain the values you want to find the median of, and the other column should contain the corresponding weights for each value.
   
2. Next, you need to sort the data in ascending order based on the values column. To do this, select both columns of data, go to the "Data" tab in the ribbon, and click on "Sort". Choose the column with the values as the sort by column, and make sure to select "Smallest to Largest".
   
3. Now, you need to calculate the cumulative weights for each value. To do this, add a new column next to the weights column and enter the following formula in the first cell:

   =SUM($B$2:B2)

   . This formula calculates the sum of all the weights from the first row to the current row. Then, copy this formula down to all the other cells in the column.
   
4. Next, you need to find the total weight of all the values. To do this, simply sum up all the weights in the weights column.
   
5. Now, you can calculate the weighted median using the following formula:

   =INDEX(A2:A11,MATCH(0.5*$D$12,B2:B11,1))

   . Here, A2:A11 is the range of values, D12 is the total weight, and B2:B11 is the range of cumulative weights. This formula finds the value that corresponds to the cumulative weight that is equal to half of the total weight.
   
6. Finally, you can display the weighted median in a cell by simply entering the formula in step 5 into the cell.

That's it! You have now calculated the weighted median in Excel. Let me know if you need any further assistance.

[vezerid]

Not so sure, but from a website defining weighted median:

To calculate the weighted median of a set of numbers you need to find
the median and if this number does not exist in the recordset take the
average of the values above and below the median instead.

Weighted Median of 1,2,3,4,5 is 3 (Median is also 3)
Weighted Median of 1,2,3,4,5,6 is 3.5 (Median is also 3.5)
Weighted Median of 1,2,4,4,4,7,7,8,8,8 is 5.2 (((4+4+4) + (7+7))/5)
(Median is 5.5)

If this is so, then the following *array* formula will calculate the
weighted median of the numbers in A2:A11:

=(SUMPRODUCT(A2:A11*(A2:A11=MAX(IF(A2:A11<=MEDIAN( A2:A11),A2:A11))))
+SUMPRODUCT(A2:A11*(A2:A11=MIN(IF(A2:A11=MEDIAN(A 2:A11),A2:A11)))))/
(SUMPRODUCT(--(A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11),A2:A11))))
+SUMPRODUCT(--(A2:A11=MIN(IF(A2:A11=MEDIAN(A2:A11),A2:A11)))))

Array formula: commit with Shift+Ctrl+Enter

HTH
Kostis Vezerides


On Jun 24, 7:38 pm, ac wrote:
Anybody know how to do it in Excel?

[Bob Phillips]

David Hager posted this UDF way back

Function WeightedMedian(ValueRange As Range, WeightRange As Range)

Dim MedianArray()

On Error GoTo WrongRanges

ArrayLength = Application.Sum(WeightRange)
ReDim MedianArray(1 To ArrayLength)

Counter = 0
ArrayCounter = 0

For Each ValueRangeCell In ValueRange

LoopCounter = LoopCounter + 1
FirstArrayPos = ArrayCounter + 1
ArrayCounter = ArrayCounter + Application.Index(WeightRange,
LoopCounter)

For n = FirstArrayPos To ArrayCounter

MedianArray(n) = ValueRangeCell.Value

Next

Next

WeightedMedian = Application.Median(MedianArray)
Exit Function

WrongRanges:
WeightedMedian = CVErr(2016)
End Function

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"ac" wrote in message
...
Anybody know how to do it in Excel?

[Harlan Grove[_2_]]

vezerid wrote...
....
=(SUMPRODUCT(A2:A11*(A2:A11=MAX(IF(A2:A11<=MEDIAN (A2:A11),A2:A11))))
+SUMPRODUCT(A2:A11*(A2:A11=MIN(IF(A2:A11=MEDIAN( A2:A11),A2:A11)))))/
(SUMPRODUCT(--(A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11),A2:A11))))
+SUMPRODUCT(--(A2:A11=MIN(IF(A2:A11=MEDIAN(A2:A11),A2:A11)))))
....

Or

=AVERAGE(IF((A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11), A2:A11)))
+(A2:A11=MIN(IF(A2:A11=MEDIAN(A2:A11),A2:A11))),A 2:A11))

[ac]

Thanks. I should have been more specific. I have a column of weights and a
column of data. I want the weighted median of the data. Is there a way to do
this using the separate range of weights?

"Harlan Grove" wrote:

vezerid wrote...
....
=(SUMPRODUCT(A2:A11*(A2:A11=MAX(IF(A2:A11<=MEDIAN (A2:A11),A2:A11))))
+SUMPRODUCT(A2:A11*(A2:A11=MIN(IF(A2:A11=MEDIAN( A2:A11),A2:A11)))))/
(SUMPRODUCT(--(A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11),A2:A11))))
+SUMPRODUCT(--(A2:A11=MIN(IF(A2:A11=MEDIAN(A2:A11),A2:A11)))))
....

Or

=AVERAGE(IF((A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11), A2:A11)))
+(A2:A11=MIN(IF(A2:A11=MEDIAN(A2:A11),A2:A11))),A 2:A11))

[vezerid]

Impressed as ever...

Regards,
Kostis

On Jun 24, 8:27 pm, Harlan Grove wrote:
vezerid wrote...

...=(SUMPRODUCT(A2:A11*(A2:A11=MAX(IF(A2:A11<=MED IAN(A2:A11),A2:A11))))
+SUMPRODUCT(A2:A11*(A2:A11=MIN(IF(A2:A11=MEDIAN( A2:A11),A2:A11)))))/
(SUMPRODUCT(--(A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11),A2:A11))))
+SUMPRODUCT(--(A2:A11=MIN(IF(A2:A11=MEDIAN(A2:A11),A2:A11)))))

...

Or

=AVERAGE(IF((A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11), A2:A11)))
+(A2:A11=MIN(IF(A2:A11=MEDIAN(A2:A11),A2:A11))),A 2:A11))

[Harlan Grove[_2_]]

ac wrote...
Thanks. I should have been more specific. I have a column of weights and a
column of data. I want the weighted median of the data. Is there a way to do
this using the separate range of weights?
....

More clarification needed. I'm guessing your mean something like the
data being in a single column range named D, weights in an adjacent
single column range named W with each row having the data value and
its corresponding weight. If so, then the weighted mean would involve
sorting the 2-column range on the D column, then calculating the
running sum of the W column and finding the median of the running
sums, and interpolating to find the D value.

For example, given the original D-W table

3 1
2 1
4 2
1 1
4 2
4 2
1 2
6 1
3 2
5 1

Sorting on D gives

1 1
1 2
2 1
3 1
3 2
4 2
4 2
4 2
5 1
6 1

Then adding a 3rd column with the running sum of W gives

1 1 1
1 2 3
2 1 4
3 1 5
3 2 7
4 2 9
4 2 11
4 2 13
5 1 14
6 1 15

The median of the running sum of W is 8, and the interpolated D value
is 3.5. If this is what you mean, then I think the udf Bob Phillips
gave would be the best approach.