Average Salary (Comp) for all EE's in same roles

[samyscraps01]

Hi, I'll do my best to describe. This is a comp report that I pulled with the compensation for all employees in one company. The first step is to find the average salary for all the employees that sit in the same role.

e.g. the average salary for all carpenters, office managers, accountants, etc.

Sam Accountant $80000
Jack Carpenter $50000
Lucy Office Mgr $65000
Joe Carpenter $55000
Cory Office Mgr $62500

the second step is when I hand in this report, how do I hide or mask when I have one employee in each role (e.g. accountant) to not giveaway his exact salary? e.g. I can have 3 carpenters and have that average of that but what if I have 1 accountant then it will give me his exact salary ($80k) and I don't want it to do that. the closest thing I can find is rounding up or down. I don't know how to address that problem but I do need to include what the average salary is even if I only have 1 employee sitting in that role.

Any suggestions greatly appreciated.

Thanks.

 

[Asbestos_Jen]

You could try something like this, and hide column E in the final form.

Book1
	A	B	C	D	E	F	G
1	Name	Role	Salary		Roles	Average by role	Masked
2	Sam	Accountant	$80,000		Accountant	$80,000	Role masked
3	Jack	Carpenter	$50,000		Carpenter	$52,500	Carpenter
4	Lucy	Office Mgr	$65,000		Office Mgr	$63,750	Office Mgr
5	Joe	Carpenter	$55,000		Plumber	$60,000	Role masked
6	Cory	Office Mgr	$62,500
7	Jen	Plumber	$60,000
Sheet2

Cell Formulas
Range	Formula
F2:F5	F2	=AVERAGEIF(B2:B7,E2,C2:C7)
G2:G5	G2	=IF(COUNTIF($B$2:$B$7,E2)=1,"Role masked",E2)

 

[samyscraps01]

Hi, this worked really well, the only issue I am having with masking the role is that then I hide the role altogether. I still need to show what an accountant makes; the only problem is I have many roles that only have one employee in it. the accountant is only on example. Essentially the people that will see this report will know that accountant makes 80k since I don't have more than 1 person to average out the salary. What else can I do to still show an average salary? These roles don't have a min or a max set up. Should I round up or down to mask the true salary amount? What other options might I have? When I say round up, if he makes $80 do I round up to $85k. I am not sure how to approach this issue without giving away exact salary amount.

 

[Asbestos_Jen]

I'm don't know what your situation is, so I don't think I can help you decide how to hide the salaries. Glad to have helped with the formula.