If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I calculate a weighted median?
Anybody know how to do it in Excel?
|
#2
|
|||
|
|||
How do I calculate a weighted median?
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? |
#3
|
|||
|
|||
How do I calculate a weighted median?
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? |
#4
|
|||
|
|||
How do I calculate a weighted median?
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)) |
#5
|
|||
|
|||
How do I calculate a weighted median?
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)) |
#6
|
|||
|
|||
How do I calculate a weighted median?
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)) |
#7
|
|||
|
|||
How do I calculate a weighted median?
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. |
Thread Tools | |
Display Modes | |
|
|