calculate probability and draw a cdf in Excel

manxing picture manxing · Feb 22, 2012 · Viewed 11.6k times · Source

Now I have a column of data like this:

0.000000
0.000000
0.000000
0.000000
0.024995
0.024996
0.024996
0.024997
0.024997
0.024997
0.024997
0.025004
0.025010
0.025011
0.025996
0.025996
0.025996

First I want to calculate the cumulative probability of these data, and show them in column B, then based on Column A and B, to draw a CDF graph.

Anyone one knows what formula should I use?

Answer

Excellll picture Excellll · Feb 22, 2012

In the cell to the left of the first entry (B1 in my example), enter the following:

=COUNT(A$1:A1)/COUNT($A$1:$A$17)

Then fill this down the column.

To create the CDF chart, create a scatter plot (with interpolated lines) with x-values =A1:A17 and y-values =B1:B17.

Note:
Since you have several duplicate values at the start of your data, you may want to plot only x-value =A4:A17 and y-values =B4:B17. This is really depends on the nature of your variable. You can do it this way if it's clear the minimum possible value is zero.