how to group percentile

  • Thread starter Thread starter Able Cox
  • Start date Start date
A

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.
 
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 C2:D2, copy down to D10.
Col D returns the required groupings
 
thank you.

Max said:
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 C2:D2, copy down to D10.
Col D returns the required groupings
Click to expand...
 
Back
Top