# Weighted salary by salary band

#### Mr2017

##### Well-known Member
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.

 Salary Band (£k) No. of staff Average salary Staff weighting 0 - 25 15 36% 25 - 50 20 48% 50 - 100 5 12% 100 + 2 5% 42

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

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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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:

Replies
1
Views
296
Replies
5
Views
458
Replies
0
Views
263
Replies
2
Views
301
Replies
2
Views
790

1,203,461
Messages
6,055,561
Members
444,799
Latest member
CraigCrowhurst

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

### Which adblocker are you using?

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

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