Calculating percentiles in Excel with "buckets" data instead of the data list itself

Question

I have a bunch of data in Excel that I need to get certain percentile information from. The problem is that instead of having the data set made up of each value, I instead have info on the number of or "bucket" data.

For example, imagine that my actual data set looks like this: 1,1,2,2,2,2,3,3,4,4,4

The data set that I have is this:

Value    No. of occurrences
  1              2
  2              4
  3              2
  4              3

Is there an easy way for me to calculate percentile information (as well as the median) without having to explode the summary data out to full data set? (Once I did that, I know that I could just use the Percentile(A1:A5, p) function)

This is important because my data set is very large. If I exploded the data out, I would have hundreds of thousands of rows and I would have to do it for a couple of hundred data sets.

Help!

Answer 1

I don't have an answer, but I'd love to hear yours if you figured it out. I have the same issue in which my buckets are speed categories. Thanks!