Weighted salary by salary band

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I'd like to calculated the average salary for employees based on salary bands.

The table below shows salary bands and the number of employees in each band.

What's the best way of working out the average salary by salary band?

I thought about taking the mid-point of the range then multiplying it by the staff weighting.

Eg for the first band (0-25k), the mid-point is 12.5k, the number of staff in that band is 15 and the staff weighting is 15/42 = 36% (as there are 42 staff in total). So you'd multiply 36% by 12.5k which is 4.5k. But that's obviously too low!

Has someone done this before or knows the best way to do this? You can copy and paste the data into cell A1.

Thanks in advance.

Salary Band (£k) No. of staffAverage salaryStaff weighting
0 - 251536%
25 - 5020 48%
50 - 1005 12%
100 +2 5%
42

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You need the actual salary for each individual to know the average within each band.

Unless these are slaves rowing a galley, there probably aren't many people making 12.5K, no?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,756
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top