# Calculate data distribution formula from data points

I have 50.000 pieces of data in a list.

10 random samples:

4,877; 4,165; 6,152; 5,544; 6,942; 7,409; 6,415; 5,592; 6,947; 4,965; 4,688


I'm trying to do 2 things, but unfortunately I've come to realize my math skills are severely lacking.

1. I want to create a graph that describes how the data is distributed. To be more specific, I want a graph with 0-100% on the y axis, and the data values along the x-axis in ascending order. Y for each value X should describe how many percentage of the data is higher than the X value. This should result in a graph starting at ~100% for the lowest value X, ending at 0% for the highest value X.

2. If possible, I want to derive an approximate formula for calculating Y for each value X based on the 50.000 data points provided.

Update I've come up with this graph. How do I get the most accurate formula for calculating values?

I've never tried anything like this before and don't know where to start. I've tried fiddling with Excel, but I'm not really making much progress.

How do I achieve what I'm trying to do? Any tools that can help me here? Also, does the kind of graph I'm trying to make have an official name?

• For a normally distributed data, one should be able to calculate the proportion of values above or below a particular value using mean and standard deviation or variance of the data (en.wikipedia.org/wiki/Normal_distribution).
– rnso
Dec 31, 2014 at 4:23
• Small point: what you call "10 random samples" is in statistical terms a single sample of size 10. Whether it's random depends on how it was selected. Dec 31, 2014 at 9:47

What you are graphing is a cumulative distribution function ("cdf"). If your data samples were generated from a probability density function and you know what that function is, then you can find the formula for the cdf by computing its integral. If not, you'll have to do some empirical curve fitting if you want a formula for predicting Y from X.

(Before I continue - if all you want to do is predict the Y value from an X value, you don't necessarily have to compute a closed-form mathematical formula; following the instructions for "Part 2" of Avraham's answer above will get you what you want. To be more specific, say your 50000 data points are in rows 1 to 50000 of column A, and you want the percentage that are above (say) X=6540, then just type =COUNTIF(A1:A50000, ">6540") / COUNT(A1:A50000) into any cell to get the percentage of the data points that are greater than that value of X.)

Okay, so let's say you want to fit a simple formula to this data. What you are looking at is some kind of sigmoid function; most cumulative distribution functions take this form. Hard to know exactly what sort of sigmoid without knowing where the data came from, but for ease of calculation, let's go with the general form of the sigmoid that you see at the top of the Wikipedia article about on the sigmoid function, which looks like this in Excelese (try plotting it with values of X ranging from -6 to 6):

=1/(1+EXP(-X))


To match your graph, we want to flip it around. In addition, we'll add two new variables. One we'll call A, it controls the sharpness of the drop-off (values greater than 1 are sharper; positive decimal values closer to 0 are flatter. You'll probably need a value pretty close to zero). Another we'll call B - it's just the horizontal translation (how much the graph gets pushed to the left or right... probably should be a pretty large number to fit your data). The resulting formula is:

=(1-(1/(1+EXP(-((X-B)*A)))))


At this point it becomes a matter of parameter fitting - i.e. figuring out what values of A and B minimize the sum of squared differences between your data and the output of this function. You can do this with Excel via the Solver plugin, or with Eureqa Desktop. Although this method will probably not give you an exact function for the cdf, it should give you a pretty good fit.

• The thing that's been described and graphed in the question is not a cdf but its complement ($1-F$, not $F$). It's known as the survivor function (or sometimes, survival function). cdfs are monotonic nondecreasing, so even the most casual glance makes clear it's not that Dec 31, 2014 at 2:50
• Technically, yes, but only because he happened to define things such that Y indicates the % of the data that is higher than the X value, rather than the % that is lower. But fair enough, point taken. Dec 31, 2014 at 9:09
• Other names encountered: reverse, converse or complementary distribution function and reliability function. Dec 31, 2014 at 9:46

### Part 1

What you want is a graph of the survival function. If you are using Excel, the simplest way to do it would be to sort the 50,000 data points in ascending order, and in the next column, start at 1 and as you go down the rows, subtract 1/50K from the value in the cell above. No you can create a scatter chart where X is column 1 and Y is column 2. If you have access to R, you can use the quantile function to do something similar. While calculating quantiles can be tricky (R has 8 or 9 versions), the above Excel method should be a good approximation, especially with 50K data points.

### Part 2

While the answer actually depends on the distribution of the underlying random variable $X$, if we continue to use the empricial distribution (the seen values, not fit to any curve), in Excel the formula would be the following translated into Excelese: Y_i = countif(XRange, "> X_i") / count(XRange).

• Thank you. I will continue working a bit with it. After arranging the excel document like you suggested it turns out my values are fairly close to a linear curve which makes the formula calculation easy Dec 30, 2014 at 19:55
• Turns out Excel tricked me. How would I go about finding the formula for this graph? dl.dropboxusercontent.com/u/11924984/valuedistribution.png Dec 30, 2014 at 20:11
• Insert Graph, type is scatter (use the version with lines, not points), and when you select data, set the X and Y ranges as the data and the emprirical survival function respectively. Dec 30, 2014 at 20:39