Percentile

[Randall Senn]

Is there a way to get a weighted percentile?

For example, suppose I have 567 students who take a test
and score between 30% and 100%. Naturally there will be
multiple occurances of most of the scores.

I can create a list 567 rows long and get the 80th
percentile straightforwardly using the Percentile
worksheet function, with the array being the column that
contains the scores and 0.8 as the value for k.

But instead of a list 567 rows long, is there a way to
list the score (e.g., 99%, 98%, 97%, etc.), the number of
students who received those scores (e.g., 12, 25, 30,
etc.), and find the 80th (or other) percentile from those
two colums?

Many thanks.

 

[Frank Kabel]

Hi
for creating this list use a pivot table. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[Aladin Akyurek]

It looks like you want to create grouped data and calculate Xth percentile,
presumably using the PERCENTILE function. First part is not that difficult:
It can be done by means of formulas or using pivot tables, etc. The second
part is harder, see:

http://216.92.17.166/board2/viewtopic.php?t=86726&highlight=percentile

All things considered, you'll get an expensive set up. It seems to me that
you are better off applying PERCENTILE to the original data and produce the
grouped data as a way of summary.