Weighted salary by salary band

[Mr2017]

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.
[TABLE="width: 343"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]
[TABLE="width: 483"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Salary Band (£k) [/TD]
[TD]No. of staff[/TD]
[TD]Average salary[/TD]
[TD]Staff weighting[/TD]
[/TR]
[TR]
[TD]0 - 25[/TD]
[TD]15[/TD]
[TD][/TD]
[TD]36%[/TD]
[/TR]
[TR]
[TD]25 - 50[/TD]
[TD]20[/TD]
[TD] [/TD]
[TD]48%[/TD]
[/TR]
[TR]
[TD]50 - 100[/TD]
[TD]5[/TD]
[TD] [/TD]
[TD]12%[/TD]
[/TR]
[TR]
[TD]100 +[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]42[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

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?