Weighted Median

[MattTom]

Greetings!

I have three data columns
Column A - market sector
Column B - stock weight
Column C - stock return

I would like to calculate a weighted median two ways:
1) for all the data in columns b & c irrespective of market sector
2) for only data in b & c that is also in the Technology sector (column A)

One caveat...some cells in columns B & C are blank.

Thanks!!

 

[shg]

Can you put a workbook with sample data on box or dropbox and post a link?

 

[MattTom]

Here's a small sample of the data

[TABLE="width: 250"]
<colgroup><col width="155" style="width: 116pt; mso-width-source: userset; mso-width-alt: 5668;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;" span="2"> <tbody>[TR]
[TD="class: xl65, width: 155, bgcolor: transparent"]Column A[/TD]
[TD="class: xl66, width: 89, bgcolor: transparent"]Column B[/TD]
[TD="class: xl66, width: 89, bgcolor: transparent"]Column C[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Sector[/TD]
[TD="class: xl68, bgcolor: transparent"]Weight[/TD]
[TD="class: xl68, bgcolor: transparent"]Return[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Industrials[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Consumer Staples[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Consumer Discretionary[/TD]
[TD="class: xl66, bgcolor: transparent"]0.48[/TD]
[TD="class: xl66, bgcolor: transparent"]-7.12[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Information Technology[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Health Care[/TD]
[TD="class: xl66, bgcolor: transparent"]0.93[/TD]
[TD="class: xl66, bgcolor: transparent"]6.46[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Information Technology[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Health Care[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Health Care[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Financials[/TD]
[TD="class: xl66, bgcolor: transparent"]1.29[/TD]
[TD="class: xl66, bgcolor: transparent"]-0.29[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Health Care[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Materials[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Real Estate[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Information Technology[/TD]
[TD="class: xl66, bgcolor: transparent"]2.47[/TD]
[TD="class: xl66, bgcolor: transparent"]4.96[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Consumer Discretionary[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Consumer Discretionary[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Financials[/TD]
[TD="class: xl66, bgcolor: transparent"]0.99[/TD]
[TD="class: xl66, bgcolor: transparent"]3.19[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Industrials[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Consumer Staples[/TD]
[TD="class: xl66, bgcolor: transparent"]1.06[/TD]
[TD="class: xl66, bgcolor: transparent"]1.46[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Materials[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Health Care[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Consumer Discretionary[/TD]
[TD="class: xl66, bgcolor: transparent"]1.05[/TD]
[TD="class: xl66, bgcolor: transparent"]0.49[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Industrials[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Information Technology[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Health Care[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Energy[/TD]
[TD="class: xl66, bgcolor: transparent"]1.04[/TD]
[TD="class: xl66, bgcolor: transparent"]2.90[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Consumer Staples[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Industrials[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Health Care[/TD]
[TD="class: xl66, bgcolor: transparent"]1.01[/TD]
[TD="class: xl66, bgcolor: transparent"]0.94[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

The weighted median with non-integer weights seems a little sketchy, but maybe like this:

Sort the data by Sector and Return, then ...

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Sector​

[/td][td="bgcolor:#F3F3F3"]

Start​

[/td][td="bgcolor:#F3F3F3"]

End​

[/td][td="bgcolor:#F3F3F3"]

Wgt Med​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Consumer Discretionary[/td][td="bgcolor:#CCFFFF"]

1​

[/td][td="bgcolor:#FFFF99"]

2​

[/td][td="bgcolor:#FFCCFF"]

0.49​

[/td][td="bgcolor:#CCFFFF"]B1: =MATCH($A1, $A$12:$A$39, 0)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Consumer Staples[/td][td="bgcolor:#CCFFFF"]

5​

[/td][td="bgcolor:#FFFF99"]

5​

[/td][td="bgcolor:#FFCCFF"]

1.46​

[/td][td="bgcolor:#FFFF99"]C1: {=MATCH($A1, IF($B$12:$B$39<>"", $A$12:$A$39))}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Energy[/td][td="bgcolor:#CCFFFF"]

8​

[/td][td="bgcolor:#FFFF99"]

8​

[/td][td="bgcolor:#FFCCFF"]

2.90​

[/td][td="bgcolor:#FFCCFF"]D2: =IFERROR(AVERAGE(LOOKUP(SUMIFS($C$13:$C$40, $A$13:$A$40, A2)/2 - {1,0}%,
INDEX($D$13:$D$40, B2):INDEX($D$13:$D$40, C2),
INDEX($B$13:$B$40, B2):INDEX($B$13:$B$40, C2))), "")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]Financials[/td][td="bgcolor:#CCFFFF"]

9​

[/td][td="bgcolor:#FFFF99"]

10​

[/td][td="bgcolor:#FFCCFF"]

(0.29)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]Health Care[/td][td="bgcolor:#CCFFFF"]

11​

[/td][td="bgcolor:#FFFF99"]

12​

[/td][td="bgcolor:#FFCCFF"]

0.94​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]Industrials[/td][td="bgcolor:#CCFFFF"]

18​

[/td][td="bgcolor:#FFFF99"]

12​

[/td][td="bgcolor:#FFCCFF"] [/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]Information Technology[/td][td="bgcolor:#CCFFFF"]

22​

[/td][td="bgcolor:#FFFF99"]

22​

[/td][td="bgcolor:#FFCCFF"]

4.96​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]Materials[/td][td="bgcolor:#CCFFFF"]

26​

[/td][td="bgcolor:#FFFF99"]

22​

[/td][td="bgcolor:#FFCCFF"] [/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]Real Estate[/td][td="bgcolor:#CCFFFF"]

28​

[/td][td="bgcolor:#FFFF99"]

22​

[/td][td="bgcolor:#FFCCFF"] [/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td="bgcolor:#F3F3F3"]

Sector​

[/td][td="bgcolor:#F3F3F3"]

Return​

[/td][td="bgcolor:#F3F3F3"]

Weight​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]Consumer Discretionary[/td][td]

(7.12)​

[/td][td]

0.48​

[/td][td="bgcolor:#CCFFCC"]

0.00​

[/td][td="bgcolor:#CCFFCC"]D12: =IF(A12<> A11, 0, SUM(C11:D11))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]Consumer Discretionary[/td][td]

0.49​

[/td][td]

1.05​

[/td][td="bgcolor:#CCFFCC"]

0.48​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]Consumer Discretionary[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

1.53​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]Consumer Discretionary[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

1.53​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]Consumer Staples[/td][td]

1.46​

[/td][td]

1.06​

[/td][td="bgcolor:#CCFFCC"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]Consumer Staples[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

1.06​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]Consumer Staples[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

1.06​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]Energy[/td][td]

2.90​

[/td][td]

1.04​

[/td][td="bgcolor:#CCFFCC"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]Financials[/td][td]

(0.29)​

[/td][td]

1.29​

[/td][td="bgcolor:#CCFFCC"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]Financials[/td][td]

3.19​

[/td][td]

0.99​

[/td][td="bgcolor:#CCFFCC"]

1.29​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]Health Care[/td][td]

0.94​

[/td][td]

1.01​

[/td][td="bgcolor:#CCFFCC"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]Health Care[/td][td]

6.46​

[/td][td]

0.93​

[/td][td="bgcolor:#CCFFCC"]

1.01​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

25​

[/td][td]Health Care[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

1.94​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

26​

[/td][td]Health Care[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

1.94​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

27​

[/td][td]Health Care[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

1.94​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

28​

[/td][td]Health Care[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

1.94​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

29​

[/td][td]Health Care[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

1.94​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

30​

[/td][td]Industrials[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

31​

[/td][td]Industrials[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

32​

[/td][td]Industrials[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

33​

[/td][td]Industrials[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

34​

[/td][td]Information Technology[/td][td]

4.96​

[/td][td]

2.47​

[/td][td="bgcolor:#CCFFCC"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

35​

[/td][td]Information Technology[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

2.47​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

36​

[/td][td]Information Technology[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

2.47​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

37​

[/td][td]Information Technology[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

2.47​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

38​

[/td][td]Materials[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

39​

[/td][td]Materials[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

40​

[/td][td]Real Estate[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

0​

[/td][td][/td][/tr]
[/table]