Employee Equity Calculator

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).

Screen Shot 2016-10-04 at 3.35.19 PM.png

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.

Screen Shot 2016-10-04 at 3.41.38 PM.png

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 david@gaus.systems for further information.

Screen Shot 2016-10-04 at 3.44.39 PM.png

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 david@gaus.systems.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s