Calculating percentile

[sarinky]

Dear Excel Experts,

I am currently calculating percentiles (10p, 25p, 50p, 75p, and 90p).

My current method is sort the data and use =percentile formular.

However, it takes me alot of time for the data is huge. I wonder if we can calculate percentile with sorting the data.

In my worksheet attached, I have Range 1-10 and the amounts which apply to the range. And I need a formular that can calculate the percentiles so that I just drag the formular down.

Kind Regards,
Sarin

[:) Sixthsense :)]

Can you please show the expected output for better understanding of your requirement?

[sarinky]

I want a result which is the same as in this example attached. As I stated above, my method takes me alot of time. I expect to have a formular that can drag down.

Kind Regards,
Sarin

[:) Sixthsense :)]

In F3 cell - Array Formula Requires CTRL+SHIFT+ENTER

Copy and paste the below formula in F3 cell and instead of normal enter press CTRL+SHIFT+ENTER.

Please Login or Register  to view this content.

Drag the formula down and right.....

[sarinky]

Thank you very much for your reply, however, it is still not what I want.

In range 1: the amount should be 2000 at 10p and 2100 at 90p
In range 2: the amount should be 1500 for every p
. . .
. . .

[:) Sixthsense :)]

Check the attachment file and confirm.....

[sarinky]

Thank you very much Sixthsense,

And apology, I missed your instruction. I should have pressed Ctrl+Shift+Enter

You are a super star.

[:) Sixthsense :)]

I am aware you missed that one and thanks for the feedback