# How can I estimate the Nth value (or Nth percentile) from a frequency distribution in Excel?

I have an Excel spreadsheet containing frequency counts rather than raw data. I would like to be able to find things like the 653rd value or the 95th percentile easily.

As an example, let's say the raw data is latency per packet. The frequency count data I have might look like:

``````1ms = 1234567
3ms = 34254
5ms = 33034
7ms = 6901
10ms = 76
``````

6901 packets fell into the bucket "greater than 5 milliseconds, but no more than 7 milliseconds".

Since the buckets are fairly granular, I was thinking I could do some trickery to compute some standard statistic information as if I had the raw data. Calculating mean doesn't require this, but something like median or standard deviation, or 99th percentile all rely on having a data range. Can anyone help?

-
This may be a better question for math.stackexchange.com, depending at how mathematically correct you want to be. You have to make some assumption about the internal distribution of each bucket. One could assume uniform distribution in each bucket. This would be the easiest to do and understand, but based on how exponential decreasing your example looks, this might be a pretty bad assumption. If you're okay with this assumption I could help you write a few formulas. – Jarvin Aug 16 '10 at 21:52
Actually, stats.stackexchange.com is probably better now that I think about it. But you should seperate the question out into finding the right formula, and applying those formula in excel, I'm not sure which you are struggling more with from you question and that should decided where it should be posted. If you would like it moved, an admin can migrate it to the other site. – Jarvin Aug 17 '10 at 5:36

For the frequencies you have, Excel doesn't have enough rows to hold the raw data. I've scaled down the frequencies by a factor of 100 (except the last one) so they would fit on an Excel sheet. If you run this code, you should get raw data that, when used with a formula like

``````{=FREQUENCY(\$A\$2:\$A\$13095,{1,3,5,7,10})}
``````

gives you (my scaled down frequencies) 12345, 343, 330, 69, 7. It produces random numbers within your ranges.

``````Sub MakeRawData()

Dim i As Long
Dim dRaw As Double

For i = 1 To 12345
dRaw = Rnd
Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
Next i

For i = 1 To 343
dRaw = (2 - 1 + 1) * Rnd + 1
Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
Next i

For i = 1 To 330
dRaw = (4 - 3 + 1) * Rnd + 3
Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
Next i

For i = 1 To 69
dRaw = (6 - 5 + 1) * Rnd + 5
Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
Next i

For i = 1 To 7
dRaw = (9 - 7 + 1) * Rnd + 7
Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
Next i

End Sub
``````

It's not terribly efficient and can take several minutes to run, but if you don't need to do it often, that shouldn't be a big problem.

-
Thanks for the code. My problem is actually worse than the numbers I posted. I've got 100 buckets (1ms to 100ms) and total count of 38 millon packets. – kbyrd Aug 16 '10 at 21:11
With that volume of data, perhaps storing the data in a database (Access, SQL Server Express) and manipulating the data with Excel would be a better approach? Unfortunately, I don't have any practical experience with manipulating large database data sets with Excel. Any thoughts, @dkusleika? – technomalogical Aug 17 '10 at 13:46
My initial thought is that once you get over about 30 data points per bucket, the results of the data manipulation aren't going to change. If you take the dataset produced by the code above, you'll get a median that's the same as if you have 38 million data points. If you do put 38 million data points in database, you can use ADO to extract them into VBA (search ADO at dailydoseofexcel for examples) but 38 million is going to take a while unless you're using assembler or something. – dkusleika Aug 19 '10 at 16:57

I'm about 1.5 years late to the party, but I thought I'd post just for anyone else who stumbles upon this.

I think your best bet is to interpolate a cumulative distribution function from the data you have. This may take some serious finessing and hand-waving, especially if the sample data you provided is close to the distribution you're working with. However, it sure beats the hell out of creating (and storing!) a million+ fake data points from a distribution you're just guessing at in the first place.

To get the CDF, you'll need to calculate the cumulative probability for each bucket. I assume there's always some latency, so take 0 as your minimum value with a frequency of 0. To find the CDF value at each bucket upper bound, use the formula

``````(sum of frequencies in bucket and previous buckets)/(sum of all frequencies)
``````

For the sample data you provided, the cumulative probability points would be

``````{(0,0); (1;0.943); (3,0.969); (5,0.995); (7,0.999); (10,1)}
``````

Now, for the finessing. If you have some common-sense insights into what the distribution looks like, e.g. you think somewhere around 25% of the latencies are less than 0.1 ms, you can add these intuitions to your data. If you don't have any idea what the distribution should look like, then you can just roll with what you have.

From here, you have two choices: either (a) linearly interpolate between the points you have, or (b) fit a functional form, such as a beta distribution, to your data. (a) is simpler because it requires no regression; however, it will not give you a picture that is more fine-grained than what you already have, and the calculation of frequencies requires a bit of Excel formula kung fu. (b) will provide you a fine-grained picture that more than likely resembles the underlying data more closely than the linear interpolation, and it only requires straightforward, simple Excel formulas to get frequencies for any bucket or percentile; however, it requires a regression, which requires the Solver add-in. I prefer option (b) because it gives you the most bang for your buck (i.e., effort).

-