how to group percentile

[Able Cox]

How to group percentile?

I have the follow time sheet of employee paid scale. I need to group into 1
to 4 pay percentile groups, for example, the lowest paid 25% into group 1.


Column A (number of employee) Column B (Pay Scale) Column C Percentile
group 1-4
23 10 1
22 12.25
37 14
12 15.50
60 17
23 19
12 20
5 22
7 25 4

Can you please tell me how to calculate this? Thank you.

 

[Max]

Assuming source payscale numbers in B2:B10
In C2: =PERCENTRANK(B$2:B$10,B2)
In D2: =VLOOKUP(C2,{0,1;0.25,2;0.5,3;0.75,4},2)
Select C22, copy down to D10.
Col D returns the required groupings

 

[Able Cox]

thank you.

Assuming source payscale numbers in B2:B10
In C2: =PERCENTRANK(B$2:B$10,B2)
In D2: =VLOOKUP(C2,{0,1;0.25,2;0.5,3;0.75,4},2)
Select C22, copy down to D10.
Col D returns the required groupings

 

[Max]

welcome