How do I calculate the median of a distribution?

[Guest]

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

 

[Guest]

use =MEDIAN()

 

[Lori]

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%.

 

[Guest]

Lori

Thank you very much, the logic is intuitive, and I'm sure your formula will
work, although I have had a bit of difficulty pasting it. Can I ask a
separate question? Where do I find detailed information on the excel
commands? In the excel help function, I cannot find anything on the prob
function that is as detailed as what you wrote (using the % sign, for
example). That makes it hard for me to understand what is really going on in
the formula.

Thank you

 

[Lori]

Help does unfortunately lack many examples of how to make good use of
it's functions. Take e.g. sumproduct.

To see how the formula above works, try it on your test data in the
example, then highlight a part of the formula e.g. prob(...) and press
F9 then press Esc to restore the original formula.

- The prob function requires that percentages/probabilities add up to
one.
The percentages in the example given add up to 100 so adding a % sign
divides each percentage by 100. Similarly if you used number of
contracts you would need to divide by sum(b1:b10) instead.

- The percentile/percentrank functions in combination can be used for
interpolation. They return the relative position in the array of a
value and are inverse operations so that: percentrank(A1:A10,9)=1,
percentile(A1:A10,1)=9.