Ranking Percentages

[hellomynameiskristin]

Ok Folks, you have never let me down before, here goes:

I have a list of percentages:

14%
44%
24%
10%
99%
54%
3%
7%
98%

(its actually a really long list of percentages) I would like a formula that would tell me in order from Highest to lowest, ranking the percentages. So I can tell *These* itemed percentages make up the top 25% of percentages, etc.

I tried: =PERCENTRANK(C5:C885,C5)

but all my values are 100%

Thanks!

 

[Lidsavr]

Try

=PERCENTILE(C5:C885,0.75)

Using the nine percentages you posted, the result is: 54% which will means anything above 54% is in the top 25%.

Try it and post again letting us know if this works for you.

Charles

 

[hellomynameiskristin]

It works in terms of coming up with a result that you mentioned. it was 82% so that means that anything over 82 is in the 25th %tile. But thats not what I am really going for I guess.

I was hoping more of a cumulative percentile so it would look like:

14% .........10%
44%.......... 50%
24%...........20
10%...........5%
99%...........100%
54%...........75%
3%............1%
7%............3%
98%...........100

 

[Lidsavr]

OK, I think I understand.

Let's use a combination of the two formulas:

=PERCENTILE(C5:C885,C5)

I think that will get you what you want. Here is what I get when I apply =PERCENTILE($C$1:$C$9,C1) = 7%

14%...................7%
44%...................19%
24%...................10%
10%...................6%
99%...................99%
54%...................30%
3%................... 4%
7%................... 5%
98%................... 99%

Please let me know how that works.

Charles

 

[hellomynameiskristin]

Hm, I get #NUM! now....

ETA: I figured out whats wrong (I think) I have values over 100% and also negative %'s...because the formula works for normal %'s within 0-100 range, but any of the outliers gets the #Num!

Bummer. Maybe I will just calculate a ranking based on a number range it falls in.