You need to calculate the cumulative percentages and interpolate the
rate for 50%.

For a single formula try the following array formula with data in
A1:B10.
(use Ctrl+Shift+Enter to execute):

=PERCENTILE(A1:A10,PERCENTRANK(PROB(A1:A10,B1:B10% ,,A1:A10),0.5,20))

Prob(...) returns the cumulative percent and the other part
interpolates at 50%.


On 1 Mar, 17:03, hello wrote:
I need to calculate the median value of a distribution of numbers. The data I
have are the percentage (pct below, or, I have the total number of contracts
if that is easier to work with) of contracts that settled at given rates
(rate). The data show that 2.9% of contracts settled at 0, 3.4% of contracts
settled at 1, etc. I need to know the median rate of settlement for this set
of contracts. There are many contracts, so it is not feasible to create a
separate row for each contract and to take the median of those values. Can
anybody help?

Many thanks in advance

Rate Pct
0 2.9
1 3.4
2.5 37.0
3.5 41.8
4.5 9.1
5.5 2.9
6.5 1.9
7.5 0.0
8 1.0
9 0.0