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

Question

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

So, 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?

Answer 1

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.

Answer 2

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