How to calculate Percentile for a large data set?

[Aryaprakash]

I want to know how to calculate percentile for a large data set. I have to calculate percentiles of 5th,10th,20th,30th,40th,50th,60th,70th,75th,80th,90th and 95th for a known value of data lets say.

1.456
1.234
1.457
1.453
1.789
1.670
1.543
1.234
1.342
1.568

I can do this by putting the formula like example=PERCENTILE.INC(IF(B:B=F3,A:A),0.05) for 5th-95th.
I want to do it for 50 states and 5 territories. That means I have to do it manually every time for each state and each territory. Is there a way in excel where I can put the formula and calculate the same thing faster for 50+5 times without doing it manually?

 

[estevaoba]

Not sure I understand what you are trying to accomplish.

What is it in your formula that you have to change manually?

What first occurred to me was the "k", so you could try this if your data starts in A1, and copy down to row 19:

=PERCENTILE.INC($A$1:$A$10,ROW()*0.05)

<tbody>
</tbody>

 

[Aryaprakash]

Ok. So for all the percentiles I can put the formula and drag down/confirm to populate the values. I want to do that for let’s say 50 times. Is there a simple way to populate it? I think I m just messing around with it which is taking lot of time.

 

[estevaoba]

If you have your values columns lined up, first row at, say A1, and stretching all the way thru BC1 (55 columns), and down to as many rows as necessary, I would paste formula below, and copy it across another 55 columns, either in the same tab or another, and down for 19 rows to the 95th percentile.

=PERCENTILE.INC(A$1:A$10,ROW()*0.05)
(unlocking columns so it will cover subsequent columns, check number of rows for your values)

That would be a one time job, for next time, you'll just have to copy/paste new values.