identifying percentile range to perform averages

wilshire · #1 06-23-2009, 07:47 PM

I work with groups of scores in the thousands and want to find a method of breaking the whole into percentile ranges and then finding the average of each range.

I can use =average(c1:c60), =average(c61:c120) in each cell up to 100 for a group of 6000, but my problem is that the 6000 is not fixed and will increase over time.

Plus I would like to find the averages for percentile ranges of subsets of varying sizes of the large group.

I'm hoping there is a simple way to do this, but this new user's searches have not found an answer.

Thanks.

**Paul** · #2 06-24-2009, 11:30 PM

Hello Wilshire, and welcome to the forum.

I'd recommend posting a sample workbook with some data, and also show the results you're expecting. This will help us help you, and hopefully save time.

Thanks!

wilshire · #3 06-25-2009, 01:10 AM

I've tried to build a small version of the workbook, with 900 scores in a column, split among four graders.

If the 900 were a constant number, I could find the results by using =average(a2:a10) through =average(a893:a901). But the 900 is not a constant but is continually growing (the real collection is over 6400 and gains about 100 new scores every day and will do so for a number of weeks).

So I'm looking for a formula that will allow me to find the average of the first 1.0% of all scores, the second 1.0% of all scores, the third 1.0% of all scores, et cetera.

In the sample workbook I've only added averages through the 12th 1.0% range. (And don't know if this matters, but this sample was created at home in 2007 but at work the original is in 2003).

EDIT: Since I mentioned wanting to look at smaller subsets, I added one for Grader a, but I realize that it would be best to deal with one question at a time - so for the moment ignore the subset to the right within the workbook.

Welcome to the Excel Forum

Forum Statistics