Your problem interested me so I spent some time working on it. There doesn't seem to be an easy solution, because median is about the "middle value" and so cannot be calculated from weights, etc. The best I could come up with without writing a macro was a set of hideous formulas.
Assuming your values are in cells A1 to E5 and counts are in A2 to E2 add the following formulas to the cells shown:
F2: =SUM(A2:E2)
A3: =IF(A$2=0,0,IF(ISEVEN($F$2),IF($A$2>$F$2/2,1,IF($A$2=($F$2-1)/2,0.5,0)),IF($A$2>=($F$2+1)/2,1,0)))
B3 and copy to C3:E3: =IF(B$2=0,0,IF(ISEVEN($F$2),IF(SUM($A$3:A$3)=0.5,0.5,IF(SUM($A$3:A$3)=1,0,IF(SUM($A$2:B$2)>$F$2/2,1,IF(SUM($A$2:B$2)=$F$2/2,0.5,0)))),IF(SUM($A$2:B$2)<($F$2+1)/2,0,IF(SUM($A$3:A$3)=1,0,1))))
F3: =SUMPRODUCT($A$3:$E$3,$A$1:$E$1)
The result is in F3
That works for my test data and should work for any data. If you have more then 5 values you need to stretch all the ":$E" references to the right and move the "F" cells.
The conditional (IF) formulas test for even number of samples (median is midway between two samples) or odd number of samples (median is on a sample) and deal with all the exceptions (no samples, median between two samples of same value etc).
The A3 formula is different to B3:E3 because there are no samples to the left.
You could incorporate the sum in F2 into the formulas in A3 to E3 if you wanted to get rid of F2. The row 3 formulas have to go below the data but F3 can go anywhere
Does this help?
