How to calculate in Excel what percentile corresponds to which score? 2

[katrina11]

Having a list of 570 scores and I need to calculate the percentile "50th and above" , "10th to < 50" "less 10th" and "insufficient"(for empty)of every test score and what percentile corresponds to which score

Actually I googled some related article like for ex these ones:

http://www.itechtalk.com/thread10579.html

http://www.excelforum.com/excel-general/514082-calculating-percentiles-of-test-scores.html

http://www.officekb.com/Uwe/Forum.a...alculate-Percentile-By-grouping-Data-in-excel

They point to the formula:=PERCENTILE(array,k)

However I still do not how to figure out what percentile corresponds to what score?

How would I assign the range among different scores in order to use this formula? I mean where should I assign k=0.5 for "50th and above" percentile and where to "10th to < 50" (and how?)percentile, "less 10th" percentile.

The only range which is somewhat obvious for me is "insufficient"....which is empty cells but still ...

I am confused. Could you please help me with it?
Any help would greatly appreciated.

Thank you!

Katrin

 

[Loomah]

hi
try using percentrank instead

in cell b1 =percentrank($a$1:$a$100,a1)
in cell c1 = choose(match(b1,{0,0.1,0.5},1),"zero","10 - 50","50+")


;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

hi - again
i might have misinterpreted you query due to not being a statistitian in any way at all!

if my previous post is rubbish ie it will give you 50% of your scores are greater than the 50th rank then you will probably need some feeder cells to calculate the percentiles

k in the syntax refers to the percentile you wish to calculate so in the case of the 50th k = 0.5 as you already know. however you could also use median value there.

anyway in a convenient place enter the following formulas in successive rows - eg a1 to a4
=max(your range) OR =percentile(your range,1)
=median(your range) or =percentile(your range,0.5)
=percentile(your range,0.1)
=min(your range) or =percentile(your range,0)

then in the column next to your data enter, assuming your scores are in col b,
=choose(match(b1,$a$1:$a$4,-1),"50+","10-50","<10")

sorry for mucking it up the first time!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

i will sort myself out in a minute and stop posting without checking what i'm saying

change the min formula to =min(your range)-1, ie you need a value lower than your minimum val and i don't know if you have negative values

change the main formula to
=choose(match(b1,$a$1:$a$5,-1),"50+","10-50","<10","empty")

that should pick up the min val correctly and show missing values as "empty"

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

hi - for the last time today!
just did a quick check and both methods allocate the same groupings to the data i was using so it seems both are valid!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[katrina11]

Looman,

It looks like the very first solution works! Thank you so much!