Employee Equity Calculator
When weighing pay equity for an employee group it is often imperative to rank your employees by compensation. There are many ways to calculate pay equity within your organization. The method we will review today uses two quick formulas to allow you to draw some quick conclusions about pay equity within a group of employees. There are many additional factors to pay equity that I’m not addressing in this post. This allows you to quickly begin building a pay equity model.
Percentile Model | Rank Formula
The model we are reviewing today, I call the percentile model. Out of all of our employees who is the closest to the 20th percentile mark? Or, whose compensation is higher than 80% of the remaining employees? The first formula to deploy is the rank formula. A very simple formula, =rank(compensation amount, to all other compensation amounts & return the rank).
Data Points Percentile Formula
This formula is used to calculate which employee’s compensation is at the 20 percentile mark. To calculate you need to use the formula p(n+1). Where p = .20 and n = number of employee records. I usually roundup so I have an exact number, therefore my formula “looks up” the exact employee.
Percentile of Employee Compensation
I then use the result of the p(n+1) percentile formula to run a vlookup to reveal which employee is at the 20 percentile point. This employee has a salary higher than 80% of all other employees. You can now begin to add additional identifying factors such as age, race, and sex to begin to build a better profile of each employee at pivotal percentile points.
I’m happy to send you this google sheet. E-mail me at firstname.lastname@example.org for further information.
These formulas can be used for other career averages within the compensation world! Have fun. Download Excel File >>> Percentile of Employee Compensation. Need assistance? Call me at 419-721-5000 or email to email@example.com.