# Formula to calculate employee cost when total salary is given and industry average per employee is given

#### Waulk

##### New Member
Hi,

Please help with formula or a method to calculate per employee job level salary cost for different departments when we only have is total salary cost employee for entire dept. We are also given average salary for each level of employee.

Below are the details

Average salary per Job level per month

JL 1 - 200 \$
JL 2 - 300 \$
JL 3 - 400 \$
JL 4 - 550 \$
JL 5 - 700 \$
JL 6 - 900 \$

Total actual cost for a department - 20000 \$

Below are the actual headcount given

JL 1 - 35
JL 2 - 25
JL 3 - 15
JL 4 - 7
JL 5 - 2
JL 6 - 1

We need to find per employee salary cost with reference to total cost given @ 20 K USD and with reference to average salary cost given per job level.

Thanks

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### GraH

##### Well-known Member
Dangerous to work with unknown parameters and averages without knowing deviations.
Maybe like this? Since you know the total and based on averages to have a theoretical total which is the either above/below the given total. Then you aply this delta based on headcount.
Not very scientific, and most likely there are better approaches. I just don't know them.
Book1
BCDEFGHIJ
1LevelAvrg PayHeadCountCorrection on Total CostNew TotalNew Avrg
2JL 1\$ 20035\$ -2.738\$ 4.262\$ 122Total on Avrg\$ 26.650
3JL 2\$ 30025\$ -1.956\$ 5.544\$ 222Known Total\$ 20.000
4JL 3\$ 40015\$ -1.174\$ 4.826\$ 322Delta\$ -6.650
5JL 4\$ 5507\$ -548\$ 3.302\$ 472
6JL 5\$ 7002\$ -156\$ 1.244\$ 622
7JL 6\$ 9001\$ -78\$ 822\$ 822
885\$ -6.650\$ 20.000
Sheet1
Cell Formulas
RangeFormula
E2:E7E2=D2/\$D\$8*\$J\$4
F2:F7F2=C2*D2+E2
G2:G7G2=F2/D2
J2J2=SUMPRODUCT(C2:C7,D2:D7)
J4J4=J3-J2
D8:F8D8=SUM(D2:D7)

#### Waulk

##### New Member
Dangerous to work with unknown parameters and averages without knowing deviations.
Maybe like this? Since you know the total and based on averages to have a theoretical total which is the either above/below the given total. Then you aply this delta based on headcount.
Not very scientific, and most likely there are better approaches. I just don't know them.
Book1
BCDEFGHIJ
1LevelAvrg PayHeadCountCorrection on Total CostNew TotalNew Avrg
2JL 1\$ 20035\$ -2.738\$ 4.262\$ 122Total on Avrg\$ 26.650
3JL 2\$ 30025\$ -1.956\$ 5.544\$ 222Known Total\$ 20.000
4JL 3\$ 40015\$ -1.174\$ 4.826\$ 322Delta\$ -6.650
5JL 4\$ 5507\$ -548\$ 3.302\$ 472
6JL 5\$ 7002\$ -156\$ 1.244\$ 622
7JL 6\$ 9001\$ -78\$ 822\$ 822
885\$ -6.650\$ 20.000
Sheet1
Cell Formulas
RangeFormula
E2:E7E2=D2/\$D\$8*\$J\$4
F2:F7F2=C2*D2+E2
G2:G7G2=F2/D2
J2J2=SUMPRODUCT(C2:C7,D2:D7)
J4J4=J3-J2
D8:F8D8=SUM(D2:D7)
Thanks a lot for your response and this seems to be a very good approach with the data we have.

Replies
6
Views
414
Replies
1
Views
165
Replies
6
Views
152
Replies
14
Views
776
Replies
2
Views
292