percentiles

[koochandkai]

I need to calculate the time for which 90% of responses lie. The data is in two columns like this:
Minutes Number of Instances
1 5
2 3
3 1
4 1

For this example, 90% of the responses are 3 minutes or less.

(Note that the number of minutes are single digits and the number of instances are single digits. I can't get the spacing quite right on the post)

I don't think the percentile function will help me -- at least not as I'm trying to apply it.

Note that I'm generating the base table from a pivot table of "times" and "counts of times". I could set up the pivot table differently if this would help solve my dilemma.

Any ideas?

Thanks much!

[ChemistB]

Can you use Percentile on your original data (selecting all times)? That should work. Use ROUND or INT if you want a whole number returned.

[koochandkai]

That was a DUH moment! Sadly, I may never have thought of that. Thanks much.

[koochandkai]

I was all hyped up for this easy solution, until...... I need to filter the data and then figure the percentiles on the filtered data only -- and in fact. I have three fields I filter on with one of them having 75 options. The pivot tables gave me counts, but was also an easy way to deal with the filtering issue.

Any other ideas?

[ChemistB]

Maybe you can give us some idea on how your data is laid out then. By filtering options, are you grouping times by certain criteria?

[koochandkai]

I'm not grouping times by certain criteria, but grouping based on other factors, and then determining the time statistic.
For a given activity, I have, for example
Identifier
Type of activity
When the activity started
When the activity ended
Who led the activity

I need to answer the following:
For all activities that started in the morning and were led by John, what's the 90%ile on how long they took
For all activities that started in the morning and were led by Bob, what's the 90%ile on how long they took
And what about the activities that started in the afternoon? -- what's the 90%ile for those that John led and what's the 90% for those that Bob led.

That kind of stuff...

[shg]

Post a workbook?

[koochandkai]

Example worksheet is attached. I want to calculate the 90th percentile for the times in Columns H and I. I want to do this for each area. (There are actually about 75 areas). For each area, I want to do this in hours 0 to 6 and also in hours 12-18.

Thanks.

[shg]

Your example contains one line for each of 10 areas and the same time value for each. Not much use as an example.