Percentile ranking

[e119687]

I am attempting to rank data by it's relative percentile of the column sum total.

I have attached an example where I have manually calculated the ranks from 1 to 5 for a each row based on a series of dollar values. I would love it if someone could come up with a formula that will output the rank (by row) instead of me having to do it by hand.

Column A is sorted largest to smallest and contains the data being ranked.

Column C is the rank based on every 20th percentile.

Thanks so much in advance if you can help.

 

[NARAYANK991]

Hi ,

Is this acceptable ?

Narayan

 

[e119687]

Hi ,

Is this acceptable ?

Narayan

Thanks for the awesomely fast response. It absolutely works but I added the cells in E through J t this example file as information only to help responders understand what I am trying t accomplish. I do not typically have this data available without creating the separate table first.

I was hoping for a formula that would return the same results you have accomplished but with the data found in $F$6:$F$10 data part of the equation.

Is this a possibility?

 

[NARAYANK991]

Hi ,

Try this :

=ROWS({0;0.2;0.4;0.6;0.8}*SUM($A$2:$A$352))-MATCH(SUM($A$2:A2),{0;0.2;0.4;0.6;0.8}*SUM($A$2:$A$352),1)+1

Enter this in D2 , and copy down.

Narayan

 

[e119687]

Hi ,

Try this :

=ROWS({0;0.2;0.4;0.6;0.8}*SUM($A$2:$A$352))-MATCH(SUM($A$2:A2),{0;0.2;0.4;0.6;0.8}*SUM($A$2:$A$352),1)+1

Enter this in D2 , and copy down.

Narayan

AMAZING!

Thank you again, this is a perfect and elegant solution to a problem that has been eating my lunch for some time.

Thank you, thank you, thank you.